Error when time should be 0:00


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
 
Ad

Advertisements

S

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.
 
N

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
 
B

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?
 
Ad

Advertisements

S

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top