| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
