Convert Date and Time Text to an Excel Date/Time Serial Number

[wdgor]

Any ideas on how I could convert this text string into a date/time serial number in Excel? Thanks for any assistance.

12:09:01AM Jun 18, 2022

 

[GraH]

If you have Power Query on board (Excel 2013 or higher, for Excel 2010 it can be installed as add-on).
Simply load the data to PQ and the conversion is done.

Book1
	A	B	C	D	E
1				when loaded to table
2		Column1		Column1
3		12:09:01 AM Jun 18, 2022		18/06/2022 0:09
4
Sheet1

 

[Coding4Fun]

If it helps you have a time and a date and can convert them

Assume Cell A1 has your Date/Time as Text

This would result in

 

[wdgor]

This looks like a really elegant solution but I have never used power query. I have Excel 365 and power query does show up under the data tab. When I select it and try to use data sources in the current workbook, I get "We didn't find any data sources in this workbook". I originally tried to parse out the data table (see below) thinking that I might be able to do something with it - no luck. Perhaps I need to do some YouTube investigation on how to use power query.

Time	Time2	Column3	Column4	Column5
12:08:53AM Jun 18, 2022	12:08:53AM	Jun	18,	2022​
12:08:57AM Jun 18, 2022	12:08:57AM	Jun	18,	2022​
12:09:01AM Jun 18, 2022	12:09:01AM	Jun	18,	2022​
12:09:05AM Jun 18, 2022	12:09:05AM	Jun	18,	2022​
12:09:09AM Jun 18, 2022	12:09:09AM	Jun	18,	2022​
12:09:13AM Jun 18, 2022	12:09:13AM	Jun	18,	2022​
12:09:17AM Jun 18, 2022	12:09:17AM	Jun	18,	2022​
12:09:21AM Jun 18, 2022	12:09:21AM	Jun	18,	2022​
12:09:25AM Jun 18, 2022	12:09:25AM	Jun	18,	2022​
12:09:29AM Jun 18, 2022	12:09:29AM	Jun	18,	2022​
12:09:33AM Jun 18, 2022	12:09:33AM	Jun	18,	2022​
12:09:37AM Jun 18, 2022	12:09:37AM	Jun	18,	2022​

 

[wdgor]

Coding4Fun - Looks like that might work but I have over 10,000 rows to convert. I took your formulas and put them in separate columns and Voila, the last column has most of what I need - brilliant! Thank you so much. Just one exception, the 12:08 is AM and not PM as the last column represents. That will be a problem in a calculation that goes over midnight.

12:08:53AM Jun 18, 2022

12:08:53

AM

Jun 18, 2022

0.506168981​

44730​

6/18/22 12:08 53 PM​

 

[Rick Rothstein]

One example is almost always not enough. For the example you posted, we cannot tell if the time value has a leading zero for single hour time values nor can we tell if the day number has a leading zero for dates before the 10th of the month. If the time value always has a leading zero, then this formula will work whether the day value has a leading zero or not...

=0+MID(A1&" "&REPLACE(A1,9,0," "),12,LEN(A1)+1)

 

[Coding4Fun]

Coding4Fun - Looks like that might work but I have over 10,000 rows to convert. I took your formulas and put them in separate columns and Voila, the last column has most of what I need - brilliant! Thank you so much. Just one exception, the 12:08 is AM and not PM as the last column represents. That will be a problem in a calculation that goes over midnight.

12:08:53AM Jun 18, 2022

12:08:53

AM

Jun 18, 2022

0.506168981​

44730​

6/18/22 12:08 53 PM​

Perhaps

 

[wdgor]

Rick -

One example is almost always not enough. For the example you posted, we cannot tell if the time value has a leading zero for single hour time values nor can we tell if the day number has a leading zero for dates before the 10th of the month. If the time value always has a leading zero, then this formula will work whether the day value has a leading zero or not...

=0+MID(A1&" "&REPLACE(A1,9,0," "),12,LEN(A1)+1)

There is a leading zero on the time. This is a data extraction from a recording pulse oximeter and is usually done during sleeping hours which, obviously, can include hours before or after 10PM and early morning hours. In any case, I applied your formula to both times and got the expected result - thank you very much!! Some day I hope to become more expert in Excel but it is comforting to know that there are brilliant minds out there who are willing to assist. Thank you again.

12:08:57AM Jun 18, 2022

6/18/2022 12:08:57 AM

03:03:57AM Jun 18, 2022

6/18/2022 3:03:57 AM

 

[wdgor]

Perhaps

View attachment 67380
View attachment 67381

This works. Thanks.


Both you and Rick came up with great solutions and I appreciate your efforts - they both work! Just like in medicine, there can be multiple ways to fix a problem.

 

[Rick Rothstein]

@wdgor - You are supposed to mark the message with the solution in it that you are going to use as the Answer, not your message saying ithat you have a solution.