# USA Date Time AM & PM convert to UK Time 24hr

#### ViperStripes

##### New Member
I thought I was pretty good at managing Date formats in Excel but this one has left me stumped.

The raw data field in the csv pre import is in the USA format and complicated by being AM/PM too.
,"8/30/2021 7:39:07 AM",
,"8/29/2021 11:28:40 PM",
All other columns are simple numeric or text

How to I get Excel to recognise these as dates in UK format: dd/mm/yyyy hh:mm:ss in one column not 2.
I can import it as 3 separate columns delimited by space with the 1st column set as MDY which imports the date part correctly, but that leaves me with the time spread over 2 columns, which needs further faffing to bring it back together then convert to 24hr then add back to the date.
I can use find and substitute to extract the values for date(year(), month(), day()) but that thinks it's 1905 not 2021
and various other extraction methods that are faffy and also only half a solution.

#### ViperStripes

##### New Member
Dave Patton:
The table above are all original dataset and hence are back to front i.e. m/d when using your formula on them it returns a #value error. All other 2000 rows convert correctly with your formula.

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### ViperStripes

##### New Member
Done some more examination, the ones that aren't converting with DavePotton's formula are all:
LEN = 21
AND
double digit month, single digit day AND 11 digits for the time portion i.e. 1x:xx:xx XM

If it has that combo of factors then the formula fails. i.e. Top 2 rows below work, bottom 2 fail.
 10/7/2020 1:48:14 PM len = 20 07/10/2020 13:48​ 10/8/2020 2:35:57 PM len = 20 08/10/2020 14:35​ 10/2/2020 10:45:33 AM len = 21 #VALUE!​ 10/2/2020 10:46:01 AM len = 21 #VALUE!​

#### ViperStripes

##### New Member
So working this out step by step, the If len bit of the formula needs adjusting, everything else works:
 IF(LEN(L2002)>20,2,1) d/m/yyyy 9​ dd/mm/yyyy 11​ d/mm/yyyy 10​ dd/m/yyyy 10​

So If(len(L2002)>20,2,1) needs changing to a if(find(" ",L2002.......something i.e. find the space after the d/m/y bit to work out the length, if it's 9 take 1 digit, if it's 11 take 2 digits. But if it's 10 length then that gets complicated going to need to embed AND, within embedded If's, must be an easier way. Tried if with embedded if statements with an AND for the last part and lost track of the number of the bracket placement!

#### ViperStripes

##### New Member
Got it, replace the len section with:
if(find(" ",L2002)=9,1,if(find(" ",L2002)=11,2,if(find("/",L2002)=2,2,if(find("/",L2002)=3,1))))

That gives an overall formula of:
DATE(MID(L2002,FIND(" ",L2002)-4,4),LEFT(L2002,FIND("/",L2002)-1),MID(L2002,FIND("/",L2002)+1,IF(FIND(" ",L2002)=9,1,IF(FIND(" ",L2002)=11,2,IF(FIND("/",L2002)=2,2,IF(FIND("/",L2002)=3,1))))))+MID(L2002,FIND(" ",L2002,1)+1,99)

Thanks all, especially Dave Potton

#### Rick Rothstein

##### MrExcel MVP
First, definitely import that column as Text. Once you have done that, here is the formula I came up with...
Excel Formula:
``=TEXT(DATE(MID(L2002,FIND(" ",L2002)-4,4),SUBSTITUTE(MID(L2002,FIND("/",L2002)+1,2),"/",""),LEFT(L2002,FIND("/",L2002)-1)),"dd/mm/yyyy")&" "&TEXT(MID(L2002,FIND(" ",L2002)+1,11),"h:mm")``

#### Dave Patton

##### Well-known Member
3 more that seem to work

Date and Time.xlsm
ABCD
42With Let available in 365
438/6/2021 12:18:50 PM6-Aug-2021 12:18 PM6-Aug-2021 12:18 PM6-Aug-2021 12:18 PM
4410/2/2020 10:45:33 AM2-Oct-2020 10:45 AM2-Oct-2020 10:45 AM2-Oct-2020 10:45 AM
4510/18/2020 11:09:04 PM18-Oct-2020 11:09 PM18-Oct-2020 11:09 PM18-Oct-2020 11:09 PM
9e
Cell Formulas
RangeFormula
B43:B45B43=DATE(MID(A43,FIND(" ",A43)-4,4),LEFT(A43,FIND("/",A43)-1),MID(A43,FIND("/",A43)+1,IF(OR(LEN(A43)={20,21}),1,2)))+MID(A43,FIND(" ",A43)+1,99)
C43:C45C43=DATE(MID(A43,FIND(" ",A43)-4,4),LEFT(A43,FIND("/",A43)-1),MID(A43,FIND("/",A43)+1,IF(OR(FIND(" ",A43)={9,10}),1,2)))+MID(A43,FIND(" ",A43)+1,99)
D43:D45D43=LET(s,FIND(" ",A43),ss,FIND("/",A43),DATE(MID(A43,s-4,4),LEFT(A43,ss-1),MID(A43,ss+1,IF(OR(s={9,10}),1,2)))+MID(A43,s+1,99))

Replies
24
Views
475
Replies
2
Views
425
Replies
4
Views
140
Replies
4
Views
105
Replies
4
Views
426

1,147,732
Messages
5,742,855
Members
423,759
Latest member
meb229

### 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.

### Which adblocker are you using?

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

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