I've made a timesheet using Excel 2000 which is working really well
apart from one thing.

At the beginning of April I started a new file for the year April 2006
- March 2007. In cell C1 I manually type in the number of hours rolling
over from last year and this gets added to the total of hours worked
for the first month (the workbook is divided into 13 4-week blocks).

This works fine except that one of my colleagues is rolling over a
small time deficit. When she types "-3:21" (without the quotes) the
total hours for that month displays "#VALUE!". The formula in this cell
is simply "=C19+J19+Q19+X19+C1".

I have the "1904 date system" box checked as I've read that this allows
Excel to add/subtract negative times - but in this case it's not
working.

Can anyone tell me what is wrong here?


Regards,



Gavin

I don't quite understand. If your cells are formatted for time as it
seems, what kind of answers are you getting when the individual values
should add to more than 24 hours? For the example, if you have several
cells formatted as time and enter 10:00 & 19:00 and add the times you
get 5:00 which is the correct clock time 19 hours after 10 o'clock. It
does not give you 29 hours as you would expect if were adding 10 HOURS &
19 HOURS. The example you gave, "-3:31", is not a TIME which Excel
expects in that cell, but a DURATION of 3 hours and 31 minutes; there
is no -3:31 o'clock.


--
protonLeah
------------------------------------------------------------------------
protonLeah's Profile: http://www.excelforum.com/member.php...o&userid=32097
View this thread: http://www.excelforum.com/showthread...hreadid=542709

If I typed -3:21 in a cell, I got yelled at by excel.

I could type:
=-"0:3:21"
or
=-time(3,21,0)

So I'm guessing she either preformated the cell as text, then did the data
entry...

or she prefixed her entry with an apostrophe:
'-3:21





wrote:

I've made a timesheet using Excel 2000 which is working really well
apart from one thing.

At the beginning of April I started a new file for the year April 2006
- March 2007. In cell C1 I manually type in the number of hours rolling
over from last year and this gets added to the total of hours worked
for the first month (the workbook is divided into 13 4-week blocks).

This works fine except that one of my colleagues is rolling over a
small time deficit. When she types "-3:21" (without the quotes) the
total hours for that month displays "#VALUE!". The formula in this cell
is simply "=C19+J19+Q19+X19+C1".

I have the "1904 date system" box checked as I've read that this allows
Excel to add/subtract negative times - but in this case it's not
working.

Can anyone tell me what is wrong here?

Regards,

Gavin

--

Dave Peterson

=-time(3,21,0)
should have been:
=-time(0,3,21)
(for 3 minutes, 21 seconds)

Dave Peterson wrote:

If I typed -3:21 in a cell, I got yelled at by excel.

I could type:
=-"0:3:21"
or
=-time(3,21,0)

So I'm guessing she either preformated the cell as text, then did the data
entry...

or she prefixed her entry with an apostrophe:
'-3:21

wrote:

I've made a timesheet using Excel 2000 which is working really well
apart from one thing.

At the beginning of April I started a new file for the year April 2006
- March 2007. In cell C1 I manually type in the number of hours rolling
over from last year and this gets added to the total of hours worked
for the first month (the workbook is divided into 13 4-week blocks).

This works fine except that one of my colleagues is rolling over a
small time deficit. When she types "-3:21" (without the quotes) the
total hours for that month displays "#VALUE!". The formula in this cell
is simply "=C19+J19+Q19+X19+C1".

I have the "1904 date system" box checked as I've read that this allows
Excel to add/subtract negative times - but in this case it's not
working.

Can anyone tell me what is wrong here?

Regards,

Gavin

--

Dave Peterson

--

Dave Peterson