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

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

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 * *

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

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

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

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