Forum Discussion
Using excel as a timesheet
Hi. I am using an excel spreadsheet as a timesheet - all formulas work, and values returned are accurate, but if I run into negative time, the values are represented by ####, instead of the actual negative hours/minutes. The cells are formatted as 'custom' hh:mm which doesn't seem to allow negative values.
It will add, subtract time erfectly well.
Any ideas?
With the default settings, Excel does not support negative time.
There is a workaround, but it will shift all existing dates in the workbook:
- Select File > Options.
- Select Advanced.
- Scroll down to the section 'When calculating this workbook'.
- Tick the check box 'Use 1904 date system'.
- Click OK.
- This will affect the specified workbook only.
Alternatively, use a formula such as =24*(A1-B1) to subtract the time in B1 from that in A1, and format the cell with the formula ad General or as Number. It will display the difference in decimal hours instead of as time.
2 Replies
With the default settings, Excel does not support negative time.
There is a workaround, but it will shift all existing dates in the workbook:
- Select File > Options.
- Select Advanced.
- Scroll down to the section 'When calculating this workbook'.
- Tick the check box 'Use 1904 date system'.
- Click OK.
- This will affect the specified workbook only.
Alternatively, use a formula such as =24*(A1-B1) to subtract the time in B1 from that in A1, and format the cell with the formula ad General or as Number. It will display the difference in decimal hours instead of as time.
Thank you. using the 1904 seems to have done the trick! much appreciated.
