Error when time should be 0:00

  • Thread starter Thread starter sboyd
  • Start date Start date
S

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
 
I get zero as expected. The cells aren't text are they?

--
__________________________________
HTH

Bob











- Show quoted text -
Click to expand...

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.
 
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
 
I got 0:00

--
__________________________________
HTH

Bob

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

--
__________________________________
HTH

Bob











- Show quoted text -
Click to expand...

Did you get 0:00 or 0?
 
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 -
Click to expand...

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.
 
Back
Top