Converting imported data to hours|minutes AM/PM

sony_g

New Member
Joined
Sep 16, 2014
Messages
9
I have 3000 lines of data that was imported from another database and appears as follows:

7:23PM
12:34PM
7:38PM
10:39PM
7:23PM
12:45PM

<tbody>
</tbody>

I need this to be hour: minute am/pm. Formatting does not work.

I assume it needs to be one of those “text” formulas. Can someone suggest how to write this or perhaps some other type of formula.

Cheers!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Welcome to the forum.

is it always 2 characters at the end? AM/PM or are some just A or P? Are there N for Noon and M for midnight?

If it is always 2 characters AM/PM then then you can use this simple formula in an adjacent column:

=TIMEVALUE(LEFT(A1,LEN(A1)-2) & " " & RIGHT(A1,2))
 
Upvote 0
thanks for the feedback.

I've dealt with this issue a number of times and have a much more complicated formula if they include things like N and M or don't have the minutes like 1A for 1:00 AM

Thought I'd start with the simple case.
 
Upvote 0
If you want to share that I will definitely be interested because I work with data that is imported from many sources and it is often #@&?(ed up!!!! :confused:
 
Upvote 0
1:30:00 PM
1:30pm
1:30am
130p
1p
130a
1a
12pm
1210p
12a

<tbody>
</tbody>

Just remember, you asked for it.

Here is the formula:

=IF(ISNUMBER(A1),A1,IF(ISERROR(SEARCH(":",A1)),IF(ISERROR(SEARCH("A",A1)),IF(ISERROR(SEARCH("P",A1)),"err",IF(SEARCH("P",A1)<4,TIMEVALUE(LEFT(A1,SEARCH("P",A1)-1)&":00 PM"),IF(SEARCH("P",A1)=4,TIMEVALUE(MID(A1,1,1)&":"&MID(A1,2,2)&" PM"),TIMEVALUE(MID(A1,1,2)&":"&MID(A1,3,2)&" PM")))),IF(SEARCH("A",A1)<4,TIMEVALUE(LEFT(A1,SEARCH("A",A1)-1)&":00 AM"),IF(SEARCH("A",A1)=4,TIMEVALUE(MID(A1,1,1)&":"&MID(A1,2,2)&" AM"),TIMEVALUE(MID(A1,1,2)&":"&MID(A1,3,2)&" AM")))),IF(ISERROR(SEARCH("A",A1)),IF(ISERROR(SEARCH("P",A1)),"err",TIMEVALUE(LEFT(A1,SEARCH("P",A1)-1)&" PM")),TIMEVALUE(LEFT(A1,SEARCH("A",A1)-1)&" AM"))))
 
Upvote 0
I think this could be done in a simpler way. I did this a number of years ago and have learned more tricks since then but this seems to work.
 
Upvote 0
Oh Dear God ...shoot me now !!! I got lost in the formula and could not find my way out! ...I will just keep using the simple one....

Thanks again.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top