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

ViperStripes

New Member
Joined
Sep 1, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 1, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

ViperStripes

New Member
Joined
Sep 1, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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 PMlen = 20
07/10/2020 13:48​
10/8/2020 2:35:57 PMlen = 20
08/10/2020 14:35​
10/2/2020 10:45:33 AMlen = 21
#VALUE!​
10/2/2020 10:46:01 AMlen = 21
#VALUE!​
 

ViperStripes

New Member
Joined
Sep 1, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 1, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,549
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
Joined
Feb 15, 2002
Messages
5,022
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
3 more that seem to work
Please check on your actual data

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

Forum statistics

Threads
1,147,735
Messages
5,742,869
Members
423,760
Latest member
photogfrog

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
Top