Negative Time


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
 
Ad

Advertisements

D

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.
 
D

Dave Peterson

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

peopleschampion

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

Bryan
 
G

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
 
D

Dave Peterson

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

Advertisements

Ad

Advertisements

D

Dave Peterson

I thought I was missing something neat.

(I feel better, too!)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top