Forum Discussion
Calculating difference between dates and times
Trying to calculate the difference between planned finish date/time and actual finish date/time.
When the difference is positive the answer is returned in the correct format (line 3) but when negative the answer is to large to show (lines 1 & 2).
| Planned Finish Time | Actual Finish Time | Diff |
| (10) 7:59 AM | (10) 9:00 AM | ############ |
| (10) 9:30 AM | (12) 8:30 AM | ############ |
| (12) 10:34 AM | (12) 7:00 AM | (00) 3:34 AM |
How is the fixed to show the negative difference's in the correct format?
1 Reply
With the default setting, Excel cannot display negative times.
You might specify the 1904 date system in Excel Options, but that would shift all dates by 4 years.
As an alternative, convert negative differences to positive ones, and use conditional formatting to apply a custom number format to the cells where the actual difference is negative.
See the attached workbook.
