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?
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.
