Problem: I keep track of comp time for employees. If employees work more than 8 hours, this time gets put into a bank so that they can work less time on another day. The company will generally let people go a few hours into the negative. But Excel completely freaks out when my formula results in a negative time.
- Cell E6 is -2 hours, but Excel refuses to display the value.
Strategy: The solution to this problem seems bizarre. You should make this change only on a worksheet that doesn't contain any existing date values.
Excel for Windows stores dates as the number of days elapsed since January 1, 1900. Excel for the Macintosh stores dates as the number of days since January 2, 1904. In case you are sharing files with a Mac, Excel has a setting which indicates that dates should be displayed in the 1904 system. Basically, Excel will adjust the date by 1,462 days when you choose this system.
In the figure above, -2 hours works out to 10 p.m. on December 31, 1899. Excel simply won't display dates from 1899. But if you go 2 hours before January 2, 1904, you happen to have a date and time that Excel is willing to display!
To switch to the 1904 date system, use File, Options, Advanced. Scroll down to When Calculating in This Workbook. Turn on the check box for Use 1904 Date System.
- Convert the workbook to the 1904 date system.
Excel will now display negative times.
- E6 displays correctly, but column A is wrong.
Gotcha: Use care when changing to the 1904 system. Any existing dates will instantly increase by 4 years and a day as seen above in column A.
Additional Details: If someone's opening balance is negative, you enter a time of -2:00 in E2.