How to Subtract times on 24hour clock and avoid error message in .

C

Clueless

I need to subtract 32 minutes from every cell in a long column. The format is
24 hour clock, so I tried the formula =A2-B1. This works, until it gets down
to midnight.
Example: 00:30 - 00:32 should equal 23:58. But all i get is error messages
saying there are negative numbers etc...
Which formula can I use that would recognize the 24hour clock and the
transitions from 23:59 to 00:00 midnight?
 
M

Marcelo

hi,
assuming you have 00:30 in a2 and 00:32 in b1

=if($b$1>a2,1+a2-$b$1,a2-$b$1)

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Clueless" escreveu:
 
C

Clueless

Marcelo - obrigado!
Your formula worked perfectly, and I thank you for your reply. You saved me
from entering data all day!!!
Regards from Canada,
Stef
 
B

Bob Phillips

=MOD(A1-B1,1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
T

Toppers

A1=00:30,b1=00:32

=(A1-B1)+(A1<B1)

Marcelo said:
hi,
assuming you have 00:30 in a2 and 00:32 in b1

=if($b$1>a2,1+a2-$b$1,a2-$b$1)

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Clueless" escreveu:
Click to expand...
 
M

Marcelo

Glad to help, and thanks for the feedback
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Clueless" escreveu:
 

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

Similar Threads

Calculate flying times using 24hour clock and 0000 general format 3
IF calculation of Time 2
Converting time question 5
counting cells within certain time frames - 24 hour clock 4
Peak call times/hours 2
Need to work out a timesheet with times going into next day (>24 h 5
Roundown and subtract time (Including Midnight) 3
calculating time 9

Top