Forum Discussion
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 when positive but get a message that negative results come up as "#############". How can I overcome this?
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.
3 Replies
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.
Thank you!! changing to 1900 date system worked Riny_van_Eekelen
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)
