Forum Discussion
Solved
Subtracting two dates that goes negitive
In Google sheets I subtract two times from each to get a positive or negative result ( condition positive result in green, negative in red ) When doing this in Excel, I get the correct result whe...
John-1952 You may consider to change a setting in Excel to "Use 1904 date system", as this will allow negative duration. Be aware, though, that your dates will be off by 4 years compared to using the 1900 date system, which is the default setting.
Google for "1904 date system" and you'll find plenty of sites explaining the difference between the two systems and how to change the settings in Excel.
Excel does not believe in negative time! Either format the difference as a number to show the difference as a count of days (positive or negative) or use
= ABS(date - refDate)
to show the difference as a time duration. The conditional formatting could be based on the formula
= SIGN(date - refDate)