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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,261
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 15, 2002
Messages
4,964
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
try either of the following
Date and Time.xlsm
AB
248/30/2021 7:39:07 AM30-Aug-2021 7:39 AM
258/29/2021 11:28:40 PM29-Aug-2021 11:28 PM
26or
278/30/2021 7:39:07 AM30-Aug-2021 7:39 AM
288/29/2021 11:28:40 PM29-Aug-2021 11:28 PM
29
9e
Cell Formulas
RangeFormula
B24:B25B24=--(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:B28B27=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

New Member
Joined
Sep 1, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
try either of the following
Date and Time.xlsm
AB
248/30/2021 7:39:07 AM30-Aug-2021 7:39 AM
258/29/2021 11:28:40 PM29-Aug-2021 11:28 PM
26or
278/30/2021 7:39:07 AM30-Aug-2021 7:39 AM
288/29/2021 11:28:40 PM29-Aug-2021 11:28 PM
29
9e
Cell Formulas
RangeFormula
B24:B25B24=--(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:B28B27=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

Well-known Member
Joined
Feb 15, 2002
Messages
4,964
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Feb 15, 2002
Messages
4,964
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Try the following, test it with your data.

Date and Time.xlsm
AB
298/6/2021 12:18:50 PM6-Aug-2021 12:18 PM
9e
Cell Formulas
RangeFormula
B29B29=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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,458
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

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

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

Well-known Member
Joined
Feb 15, 2002
Messages
4,964
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 
Last edited:

Forum statistics

Threads
1,143,841
Messages
5,721,110
Members
422,340
Latest member
canadianbacon357

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