How do you enter a negative time number in Excel?
 

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.

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.

On Thu, 17 Mar 2005 14:25:06 -0800, "Thomas99"
wrote:

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.

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.

Thomas99 wrote:

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.

--

Dave Peterson

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
--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"Myrna Larson" wrote in message
...
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.

On Thu, 17 Mar 2005 14:25:06 -0800, "Thomas99"
wrote:

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.

Never mind Myrna, I see that Dave has answered my question.

Regards

Sandy

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
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
--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"Myrna Larson" wrote in message
...
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.

On Thu, 17 Mar 2005 14:25:06 -0800, "Thomas99"
wrote:

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.