Time formats

[Carla]

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

 

[Bob Phillips]

Try this

=MOD(TEXT(--(B1),"00\:00")-TEXT(--(A1),"00\:00"),1)

 

[Pete_UK]

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

 

[Carla]

Thank you so much Pete! You have ended my frustration!! This worked
perfectly.

 

[Carla]

Thank you Bob for taking the time to help me out. I couldn't get this
formula working, but appreciate the assistance.

 

[Pete_UK]

Well, glad to hear it, Carla - thanks for feeding back.

Pete

 

[Bob Phillips]

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.