How do you enter a negative time number in Excel?

T

Thomas99

I am trying to create a spreadsheet to track employee time records, and I
need a way to enter "negative" time numbers.

In order to correct data entry errors, I need the ability to enter "-8:30",
for example, and include that figure when totaling the column.
 
M

Myrna Larson

Then you'll have to switch the workbook to the 1904 date system
(Tools/Options/Calculation). Be forewarned that if you already have dates on
the worksheet, they will change by 1462 days (4 years + 1 day). If that's a
problem, post back and we'll tell you how to fix it.
 
D

Dave Peterson

If you change the base year
tools|options|calculation tab|check 1904 date system

Then you can enter negative time:
=-TIME(8,30,0)

-8:30 or =0-8:30
won't work

You can give it the format you like:
hh:mm:ss
???

But be aware that any date in your workbook will now be 4 years and one day
different.

This can be a problem if you copy and paste from workbooks that use different
date systems.
 
S

Sandy Mann

Myrna,

Even switching to 1904 date system I can't get XL (97) to accept a negative
tieme entry directly. It will *accept* and show a negative time if I enter
it
as a formula as in =A1 -B1 with 00:00 in A1 and 8:00 in B1, and then
copy/paste special/values back into the same cell or
run a macro like:

Sub Test()
t = InputBox("Tim")
n = TimeValue("00:00")
n = n - TimeValue(t)
ActiveCell.Value = n
End Sub


but it will not accept a direct entry. Is it different in other versions?

Regards

sandy
 
Top