Negative Time

  • Thread starter Thread starter Hector
  • Start date Start date
H

Hector

I am trying to make up a time sheet, with these columns:
Start time, time taken for lunch, Finish time, Total hours worked, Time
worked in excess of 7:30 hours.
I have formatted Start time and Finish time as 'Time: 13:30' and the other
columns as 'Custom: h:mm'
My problem is with the "Time in excess of 7:30" - as long as the total time
is above 7:30 hrs, then there is no problem. I can subtract 7:30 hrs from
the total and get the correct answer. However, if the total hours worked is
less than 7:30 hrs, if I subtract 7:30 hrs from it, the result is ########
rather than a negative figure.
Can anyone tell me how to subtract larger periods of time from shorter
periods of time to get a correct negative result? I am using Excel 2000.
Hector
 
If you change your base date from 1900 to 1904, you can have negative times.
But this will affect dates, too. Your dates will change by 4 years + a leap
day.

Tools|options|calculation tab
check the 1904 date system box at the bottom left.
 
PS. This setting can cause lots of grief when merging multiple workbooks
together--when each has a different base date.
 
Hector

Negative times are shown as ################ in the cell. The underlying
value is there and can be used in calculations.

You can change this to show a negative serial number, but not a formatted time
such as 10:15

Format>Cells>Custom. Pick any format then delete everything in the dialog
box>OK


Gord Dibben Excel MVP
 
Back
Top