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

[ViperStripes]

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.

 

[Micron]

After spending about 30 minutes trying to get a formula all into one cell I gave up. I can show you the component parts and you decide if you can group it all or do something else (maybe that is even nothing) with it.
First, UK date is simply a matter of formatting, I'd say. I formatted 8/30/2021 1:39:07 PM as dd/mm/yyyy hh:mm and got 3.0/08/2021 13:39 (note the time result).
If shown as a number, the date is actually 44438.5688310185. I show that in case it helps to make more sense out of the math. 2nd column shows 13 hours and 65 hundredths of a day. 3rd returns just the hours. 4th just the decimal portion of a day. 5th converts that to minutes. 6th concatenates it as HH:MM

44438.57​

13.65​

13.00​

0.65​

39​

13:39

formulas per column are as follows:
3rd: =24*(B1-INT(B1))
4th: =INT(C1)
5th: =C1-D1
6th: =INT(E1*60)
7th: =D1:D1 & ":" & F1

I was able to get a few steps in to one cell but not all of it. Clunky? Probably, and maybe you can't use a template, which is what this would probably require. I suppose there is a code solution once the workbook has been created but somehow I think you'd really rather have a solution that massages data before the transport or during it. I can't imagine there's a way to manipulate data and formats during the transport.

 

[Dave Patton]

try either of the following

Date and Time.xlsm
	A	B
24	8/30/2021 7:39:07 AM	30-Aug-2021 7:39 AM
25	8/29/2021 11:28:40 PM	29-Aug-2021 11:28 PM
26	or
27	8/30/2021 7:39:07 AM	30-Aug-2021 7:39 AM
28	8/29/2021 11:28:40 PM	29-Aug-2021 11:28 PM
29
9e

Cell Formulas
Range	Formula
B24:B25	B24	=--(MID(A24,FIND(" ",A24)-4,4)&"-"&LEFT(A24,FIND("/",A24)-1)&"-"&MID(A24,FIND("/",A24)+1,2))+MID(A24,FIND(" ",A24,1)+1,99)
B27:B28	B27	=DATE(MID(A27,FIND(" ",A27)-4,4),LEFT(A27,FIND("/",A27)-1),MID(A27,FIND("/",A27)+1,2))+MID(A27,FIND(" ",A27,1)+1,99)

 

[ViperStripes]

try either of the following

Date and Time.xlsm
	A	B
24	8/30/2021 7:39:07 AM	30-Aug-2021 7:39 AM
25	8/29/2021 11:28:40 PM	29-Aug-2021 11:28 PM
26	or
27	8/30/2021 7:39:07 AM	30-Aug-2021 7:39 AM
28	8/29/2021 11:28:40 PM	29-Aug-2021 11:28 PM
29
9e

Cell Formulas
Range	Formula
B24:B25	B24	=--(MID(A24,FIND(" ",A24)-4,4)&"-"&LEFT(A24,FIND("/",A24)-1)&"-"&MID(A24,FIND("/",A24)+1,2))+MID(A24,FIND(" ",A24,1)+1,99)
B27:B28	B27	=DATE(MID(A27,FIND(" ",A27)-4,4),LEFT(A27,FIND("/",A27)-1),MID(A27,FIND("/",A27)+1,2))+MID(A27,FIND(" ",A27,1)+1,99)

DavePatton:
Both those work BUT it doesn't work when the date is a single digit for day & month, it returns a #value error e.g.
8/6/2021 12:18:50 PM which is 6th August not 8th June.

 

[Dave Patton]

With Data Text to Columns
1. use delimiter of space
2 on column a Text to Column no delimiters date MDY
3 formula to combine
4 format to your preference

 

[Dave Patton]

Try the following, test it with your data.

Date and Time.xlsm
	A	B
29	8/6/2021 12:18:50 PM	6-Aug-2021 12:18 PM
9e

Cell Formulas
Range	Formula
B29	B29	=DATE(MID(A29,FIND(" ",A29)-4,4),LEFT(A29,FIND("/",A29)-1),MID(A29,FIND("/",A29)+1,IF(LEN(A29)>20,2,1)))+MID(A29,FIND(" ",A29,1)+1,99)

 

[Rick Rothstein]

DavePatton:
Both those work BUT it doesn't work when the date is a single digit for day & month, it returns a #value error e.g.
8/6/2021 12:18:50 PM which is 6th August not 8th June.

Question: When you import your dates, are they all right aligned, left aligned or a mix of left and right aligned?

 

[ViperStripes]

DavePatton:
Your latest one worked for 2000 of the rows, just this tiny set that it didn't work on for some reason: Can't see any commonality.

10/2/2020 10:45:33 AM

10/2/2020 10:46:01 AM

10/5/2020 10:54:02 AM

10/6/2020 12:45:45 PM

10/8/2020 11:09:04 PM

11/3/2020 11:13:01 PM

11/4/2020 10:49:22 AM

11/8/2020 11:18:27 PM

11/8/2020 11:22:46 PM

11/8/2020 11:27:07 PM

11/9/2020 11:48:19 AM

11/9/2020 12:01:16 PM

11/9/2020 12:01:34 PM

11/9/2020 12:02:17 PM

11/9/2020 12:04:16 PM

12/7/2020 10:18:12 AM

12/7/2020 10:38:14 AM

 

[ViperStripes]

Question: When you import your dates, are they all right aligned, left aligned or a mix of left and right aligned?

Those that are day<=12 and month <= 12 are imported right aligned i.e. spotted as dates by Excel but incorrectly as d/m when they are m/d, but all other rows imported as left aligned i.e. text fields.
I tried Data Import rather than just opening the csv and set the column as MDY, but that had same effect.

 

[Dave Patton]

You stated " just this tiny set that it didn't work on for some reason: Can't see any commonality."
what does didn't work mean?

What is the correct result with the data you posted?
A couple of examples is sufficient.

Recommendations
-ensure that the entire range is Text
- check what options are available with the export