Error in Time when using formula
I schedule device update maintenance windows for the company I work for, and we use something called scopesheets, where we pour all the info about the schedule, the devices, the files to be used, the method of procedure, etc… In the Schedule tab, I have to specify the dates and times for different time zones, one for the tool we use to schedule the work, one for the customer time zone, and one for the implementation engineer time zone. Since it was tedious having to modify all three groups of dates and times when a modification was needed, I started to use formulas to calculate them, I enter the date and time of the tool we use, and the formulas calculate the date and time for the other two groups.
For example, for the time I use “=D10- (1/24)” being “D10” the source cell and the minus and the “1” are variables that depends on how many hours before or after the target time zone is… Now, the problem is that, if the formula has to calculate a “negative” hour, meaning that my source time is something like 1 AM, and one of the other times is something like 11 PM from the day before, I get and error that says “Dates and times that are negative or too large show as #####” and the cells displays a bunch of “#####”
Is there any way to avoid this? To make Excel show the correct time?
I schedule device update maintenance windows for the company I work for, and we use something called scopesheets, where we pour all the info about the schedule, the devices, the files to be used, the method of procedure, etc… In the Schedule tab, I have to specify the dates and times for different time zones, one for the tool we use to schedule the work, one for the customer time zone, and one for the implementation engineer time zone. Since it was tedious having to modify all three groups of dates and times when a modification was needed, I started to use formulas to calculate them, I enter the date and time of the tool we use, and the formulas calculate the date and time for the other two groups. For example, for the time I use “=D10- (1/24)” being “D10” the source cell and the minus and the “1” are variables that depends on how many hours before or after the target time zone is… Now, the problem is that, if the formula has to calculate a “negative” hour, meaning that my source time is something like 1 AM, and one of the other times is something like 11 PM from the day before, I get and error that says “Dates and times that are negative or too large show as #####” and the cells displays a bunch of “#####”Is there any way to avoid this? To make Excel show the correct time? Read More
