Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time formats
Hello,
Using Excel 2003, I am trying to convert 24 hour times (currently formatted as general) into h:mm AM/PM. My ultimate goal is calculate the difference between times, which sometimes straddles two 24 hour periods. See example data and current formatting below. Hope someone can help. Thanks, -- Carla Arrival time Departure time Difference 2335 0250 1340 0200 1400 0240 1355 0130 1300 0220 1435 0245 0730 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time formats
Try this
=MOD(TEXT(--(B1),"00\:00")-TEXT(--(A1),"00\:00"),1) -- __________________________________ HTH Bob "Carla" wrote in message ... Hello, Using Excel 2003, I am trying to convert 24 hour times (currently formatted as general) into h:mm AM/PM. My ultimate goal is calculate the difference between times, which sometimes straddles two 24 hour periods. See example data and current formatting below. Hope someone can help. Thanks, -- Carla Arrival time Departure time Difference 2335 0250 1340 0200 1400 0240 1355 0130 1300 0220 1435 0245 0730 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time formats
You can convert those values into Excel times using this:
=TIME(LEFT(A2,2),RIGHT(A2,2),0) then apply Format | Cells to the cell to set it up to display as h:mm AM/PM. If you don't want to use a helper column, then you can do this: =TIME(LEFT(B2,2),RIGHT(B2,2),0) -TIME(LEFT(A2,2),RIGHT(A2,2),0) + (B2<A2) in C2 - again, format this cell as you require (maybe just h:mm) Hope this helps. Pete On Sep 2, 7:37*am, Carla wrote: Hello, Using Excel 2003, I am trying to convert 24 hour times (currently formatted as general) into h:mm AM/PM. *My ultimate goal is calculate the difference between times, which sometimes straddles two 24 hour periods. See example data and current formatting below. Hope someone can help. Thanks, * -- Carla Arrival time * *Departure time *Difference 2335 * *0250 * * * * * * 1340 * * * * * * 0200 * * * * * * 1400 * * * * * * 0240 * * * * * * 1355 * * * * * * 0130 * * * * * * 1300 * * * * * * 0220 * * * * * * 1435 * * * * * * 0245 * * * * * * 0730 * * |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time formats
Thank you so much Pete! You have ended my frustration!! This worked
perfectly. -- Carla "Pete_UK" wrote: You can convert those values into Excel times using this: =TIME(LEFT(A2,2),RIGHT(A2,2),0) then apply Format | Cells to the cell to set it up to display as h:mm AM/PM. If you don't want to use a helper column, then you can do this: =TIME(LEFT(B2,2),RIGHT(B2,2),0) -TIME(LEFT(A2,2),RIGHT(A2,2),0) + (B2<A2) in C2 - again, format this cell as you require (maybe just h:mm) Hope this helps. Pete On Sep 2, 7:37 am, Carla wrote: Hello, Using Excel 2003, I am trying to convert 24 hour times (currently formatted as general) into h:mm AM/PM. My ultimate goal is calculate the difference between times, which sometimes straddles two 24 hour periods. See example data and current formatting below. Hope someone can help. Thanks, -- Carla Arrival time Departure time Difference 2335 0250 1340 0200 1400 0240 1355 0130 1300 0220 1435 0245 0730 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time formats
Thank you Bob for taking the time to help me out. I couldn't get this
formula working, but appreciate the assistance. -- Carla "Bob Phillips" wrote: Try this =MOD(TEXT(--(B1),"00\:00")-TEXT(--(A1),"00\:00"),1) -- __________________________________ HTH Bob "Carla" wrote in message ... Hello, Using Excel 2003, I am trying to convert 24 hour times (currently formatted as general) into h:mm AM/PM. My ultimate goal is calculate the difference between times, which sometimes straddles two 24 hour periods. See example data and current formatting below. Hope someone can help. Thanks, -- Carla Arrival time Departure time Difference 2335 0250 1340 0200 1400 0240 1355 0130 1300 0220 1435 0245 0730 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time formats
Well, glad to hear it, Carla - thanks for feeding back.
Pete On Sep 3, 12:07*am, Carla wrote: Thank you so much Pete! *You have ended my frustration!! *This worked perfectly. -- Carla |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time formats
It worked fine for me in the example that you provided, 2335 and 250. It
assumes both are text cells, but so does Pete's for your example and you say that worked so that should not be the issue. -- __________________________________ HTH Bob "Carla" wrote in message ... Thank you Bob for taking the time to help me out. I couldn't get this formula working, but appreciate the assistance. -- Carla "Bob Phillips" wrote: Try this =MOD(TEXT(--(B1),"00\:00")-TEXT(--(A1),"00\:00"),1) -- __________________________________ HTH Bob "Carla" wrote in message ... Hello, Using Excel 2003, I am trying to convert 24 hour times (currently formatted as general) into h:mm AM/PM. My ultimate goal is calculate the difference between times, which sometimes straddles two 24 hour periods. See example data and current formatting below. Hope someone can help. Thanks, -- Carla Arrival time Departure time Difference 2335 0250 1340 0200 1400 0240 1355 0130 1300 0220 1435 0245 0730 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time formats | Excel Worksheet Functions | |||
Converting time formats into actual time(minutes) | Excel Discussion (Misc queries) | |||
Help with time formats | Excel Worksheet Functions | |||
Time Formats | Excel Discussion (Misc queries) | |||
Time Formats | Excel Discussion (Misc queries) |