Negative Time

[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

 

[Dave Peterson]

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.

 

[Dave Peterson]

PS. This setting can cause lots of grief when merging multiple workbooks
together--when each has a different base date.

 

[peopleschampion]

Go to tools/options/calculation/check 1904 date system.

Bryan

 

[Gord Dibben]

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

 

[Dave Peterson]

After deleting all the custom formatting, aren't you just left with General?

 

[Gord Dibben]

Duhhh!

Thanks Dave

Gord

After deleting all the custom formatting, aren't you just left with General?

 

[Dave Peterson]

I thought I was missing something neat.

(I feel better, too!)