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

G

Guest

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

Guest

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:
 
G

Guest

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)
 
G

Guest

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

Guest

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
Peak call times/hours 2
Rounding Error when Calculating similar times 3
Subtracting hours and minutes with negative times 2
If time Go to Function 1
How To Calculate the Total on-the-clock time, by Month, in MSAcces 1
Workday calculations for weekends 9
How to calculate a Time Difference 2

Top