Subtracting Times with no space between time and AM/PM

[MR22]

I have data that pulls into a spreadsheet with times that have no spaces between the time and the AM/PM. For example Time in: 9:10AM and Time out: 3:00PM. I want to subtract the 2 so that my actual hours are 5:50. Unfortunately, it won't work because there is no space between the time and the AM/PM. It works with a space if I use =TEXT(one - the other,"h:mm"). Unfortunately to get a space into all the 1000's of lines of data it would be extremely time consuming.

Any suggestions?

Thanks!

MR

[JohnTopley]

With Start time in A1 and End time in B1

in C1

=MOD(SUBSTITUTE(SUBSTITUTE($B1,"AM"," AM"),"PM"," PM")-SUBSTITUTE(SUBSTITUTE($A1,"AM"," AM"),"PM"," PM"),1)

to handle times over midnight.

format C1 as "hh:mm"

[AlKey]

Try this
Enter formula in C1

Please Login or Register  to view this content.

Format cell C1 as Custom, h:mm

v	A	B	C
1	9:10AM	3:00PM	5:50

[Pete_UK]

Here's another one, with the times in A2 and B2:

=(LEFT(B2,LEN(B2)-2)+IF(RIGHT(B2,2)="PM",0.5,0) - (LEFT(A2,LEN(A2)-2)+IF(RIGHT(A2,2)="PM",0.5,0)))*24

This returns the result in hours, but if you want hh:mm format just remove the *24 from the end.

Hope this helps.

Pete

[Tony Valko]

One way...

Select the data in question
Press the key combo of CTRL H (this will open the Find/Replace userform)

Find what: A
Replace with: [enter a space character]A
Replace All

Find what: P
Replace with: [enter a space character]P
Replace All

That should convert the data to true time values that can then be calculated using simple formulas.

[AlKey]

Here is a shorter version

Please Login or Register  to view this content.

[JohnTopley]

@Tony,
Too easy .... !!!!

[Tony Valko]

@Tony,
Too easy .... !!!!

Of course, this could be a PITA if the data is scattered all over the place!

[Amanda Wilso]

To begin with, select the range of cells to look in. To search across the entire worksheet, click any cell on the active sheet.
Open the Excel Find and Replace dialog by pressing the Ctrl + F shortcut. Alternatively, go to the Home tab > Editing group and click Find & Select > Find All.
Now Find and Replace first A then P and adding the space for each of them.

[MR22]

This was very helpful! Thank you!