Forum Discussion

Shaun1650's avatar
Shaun1650
Copper Contributor
Jun 12, 2024

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 TimeActual Finish TimeDiff
(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?

 

 

 

excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 12, 2024

    Shaun1650 

    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.