Error when time should be 0:00

[sboyd]

I have a timesheet that has several categories to put in time.

The 1st Column has the total hours. Each of the other columns divides
the time into hours and minutes worked on different projects and the
last column assigns the remaining time. The problem is when all of
the time has been assigned, I'm getting an error in the last column
when it should display 0:00.

Example:

J26 L26
M26 N26 O26 P26

0:15 0:00
0:00 0:00 0:15 Should
display 0:00 but is giving error.

The timesheet works just fine as long as the calulation is not 0. The
cells are formatted as custom h:mm.
We are using Excel 2007.

My formula is J26-L26-M26-N26-026 (the formula in P26). What am I
doing wrong.

Thanks
sboyd

 

[Bob Phillips]

I get zero as expected. The cells aren't text are they?

 

[sboyd]

I get zero as expected. The cells aren't text are they?

--
__________________________________
HTH

Bob











- Show quoted text -

No, They are formatted as Custom - Number, h:mm. on all of the
fields. The time in each field is entered in the h:mm format.

 

[sboyd]

I get zero as expected. The cells aren't text are they?

--
__________________________________
HTH

Bob











- Show quoted text -

Did you get 0:00 or 0?

 

[Niek Otten]

You didn't say what error. Does the cell show ### ? If so:
Format as General. Maybe you see some very small negative number? Excel
can't show negative date/time.
One way out: change your date system to the 1904 system. Read HELP for all
the implications first.
Office button (large round, top left)>Excel Options>Advanced tab>When
calculating this workbook>check 1904 date system

Or use ROUND() to avoid negative times

 

[Bob Phillips]

I got 0:00

--
__________________________________
HTH

Bob

I get zero as expected. The cells aren't text are they?

--
__________________________________
HTH

Bob











- Show quoted text -

Did you get 0:00 or 0?

 

[sboyd]

You didn't say what error. Does the cell  show ### ? If so:
Format as General. Maybe you see some very small negative number? Excel
can't show negative date/time.
One way out: change your date system to the 1904 system. Read HELP for all
the implications first.
Office button (large round, top left)>Excel Options>Advanced tab>When
calculating this workbook>check 1904 date system

Or use ROUND() to avoid negative times

--
Kind regards,

Niek Otten
Microsoft MVP - Excel











- Show quoted text -

That must have been the problem. I added 1 second to the formula and
it appears to have fixed the problem. Thank you for your help.