Negative times

[[email protected]]

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

[protonLeah]

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.

[Dave Peterson]

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





[email protected] 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]

=-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
>
> [email protected] 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