Negative times are displayed a ###########

  • Thread starter Thread starter Omar
  • Start date Start date
O

Omar

Dear Experts,
How can I get the real figure if I have the following message?
Negative times are displayed a ###########.
Actually I got the above massage from the below formula.
=2:49 - 3:17, (hh:mm format)
Thanks.
 
This is because it is running over midnight.

Try

=MOD(A2-B2,1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
You can overcome that by using the 1904 date/time system, but at a price.
Tools>Options>Calculation tab, check 1904 date system
Note that all dates in your workbook will change.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Dear Experts,
| How can I get the real figure if I have the following message?
| Negative times are displayed a ###########.
| Actually I got the above massage from the below formula.
| =2:49 - 3:17, (hh:mm format)
| Thanks.
|
 
You can change the date system to 1904 system BUT all dates in your w/book
will be out by 4 years. (Tools=>Options==>Calculation)

Or convert to decimal times (multiply by 24) and format cells as general or
number.

Multiply result of subtraction by 60 to get results in minutes.

HTH
 
Omar,
You have certainly these hh:mm's in appropriate cells, say A1 and A2. For
obtaining allways the meaningful result (within 24 hours, of course) use
conditionally

=IF(A1<A2;1+A1-A2;A1-A2)

- it "borrows" a necessary day to the calculation.
 
Back
Top