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

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?

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

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

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

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



"Clueless" escreveu:

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?

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

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

"Marcelo" wrote:

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

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

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



"Clueless" escreveu:

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?

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

=MOD(A1-B1,1)

--
HTH

Bob Phillips

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

"Clueless" wrote in message
...
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?

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

A1=00:30,b1=00:32

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

"Marcelo" wrote:

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

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

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



"Clueless" escreveu:

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?

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

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



"Clueless" escreveu:

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

"Marcelo" wrote:

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

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

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



"Clueless" escreveu:

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?