Converting Time
Posted by Brigite on January 02, 2002 11:40 AM
Looking for assistance in converting time from standard to military from general format that includes am or pm. I am needing to subtract the first column from the second column. See examples below:
8:51AM 10:05AM
10:09AM 10:38AM
10:39AM 10:59AM
11:14AM 11:59AM
12:01PM 12:42PM
1:37PM 2:27PM
2:40PM 3:01PM
3:28PM 4:38PM
4:43PM 5:03PM
Posted by Aladin Akyurek on January 02, 2002 11:47 AM
Brigite --
You don't need to convert in order to subtract.
Assuming that you have these times in A from A2 on and B from B2 on,
in C1 enter: =MOD(B2-A2,1)
Custom format C1 as [h]:mm and copy down as far as needed.
Aladin
========
Posted by Scott on January 02, 2002 11:48 AM
You should be able to do this by just formating the cells. When I copied over your data into excel, it did not recognize the time because there was no space between the number and the AM/PM. If you do Edit-Replace and replace A with space A and P with space P it should work ok.
Posted by IML on January 02, 2002 11:59 AM
Why mod?
Hi Aladin,
What is the purpose the mod? This would just seem to produce undesirable results if you go over a 24 hour period, no?
for example if b2 = now() and a2 =now()-1 you get a result of zero? Just curious. I asked in a below post, but are you chiming in from Instanbul? The world is getting smaller...
Posted by Aladin Akyurek on January 02, 2002 12:46 PM
Hmmm... (Re: Why mod?)
Hi Ian --
Happy New Year. Since yesterday, started to chim in from home. They started "snowing" after I left Istanbul. Home, still nothing...
That darn MOD thing is supposed to be equivalent to
=B2+(B2 < A2)-A2
I swear I'll never propose it again.
Your "date and time antennae" did it again. Congrats and thanks.
Brigite: Please replace (if you're using it) =MOD(B2-A2,1) by the formula noted above.
Aladin
======
