Excel convert/format string back to date & time

[Nu2Java]

Hi All - I have a string that is being used as a serial number which is just created from the date/time. Does anyone know how I can convert this back to date & time? Into format 02/15/2023 12:25:00 PM I have tried using custom formats etc but nothing has worked. Thanks for any help

02152023122500
mmddyyyyhhmmss

 

[ETAF]

how about
=DATEVALUE(MID(A1,3,2)&"/"&LEFT(A1,2)&"/"&MID(A1,5,4))+TIMEVALUE(MID(A1,9,2)&":"&MID(A1,11,2)&":"&RIGHT(A1,2))

 

[Nu2Java]

Thanks @ETAF It shows #Value error in the cell on your attachment. Not sure what is causing that.

 

[ETAF]

strange
works for me

what version of excel - I'm only using text functions - Left Right and MID - so should be OK

 

[Nu2Java]

I am using Excel 365 but I will try the text on it and see if that works.

 

[ETAF]

365 should work - I'm on 365

just saved a again as a different name - closed and opened and still working OK

 

[Nu2Java]

Hmm this is very strange. I cannot get it to work. I deleted and downloaded the one you just sent and it does the same thing.

 

[ETAF]

what time zone are you in for using dates
if youe default format is MM/DD/YYYY - then that conversion will be using 15 as the month and not 15 as the day

so
=DATEVALUE(MID(A1,3,2)&"/"&LEFT(A1,2)&"/"&MID(A1,5,4))+TIMEVALUE(MID(A1,9,2)&":"&MID(A1,11,2)&":"&RIGHT(A1,2))

the first part is the day
MID(A1,3,2)
2nd part month
LEFT(A1,2)

so maybe
=DATEVALUE(LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&MID(A1,5,4))+TIMEVALUE(MID(A1,9,2)&":"&MID(A1,11,2)&":"&RIGHT(A1,2))

as i get Value error - with that
as my Date is UK - DD/MM/YYYY HH:MM:SS

 

[Nu2Java]

Ah ok I see how that is working. I am in the US, so I use MM/DD/YYYY

 

[ETAF]

I am in the US, so I use MM/DD/YYYY

thats the issue then
try
=DATEVALUE(LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&MID(A1,5,4))+TIMEVALUE(MID(A1,9,2)&":"&MID(A1,11,2)&":"&RIGHT(A1,2))
not sure if textsplit - may work - issue is the 4 character year i guess
anyway

 

[Nu2Java]

That did the trick... I really need to learn more about this kind of formula. Thanks so much for your help on this

 

[ETAF]

you are welcome

 

[Nu2Java]

Does the first "LEFT(A1,2)" change to (LEFT(A1,1) if there is only single digit month?

 

[ETAF]

yes, BUT so will every other function

so MID()

mid( string to extract , the number of characters from left , the number to extract )

so now instead of
MID(A1,3,2)
then the month is no longer the 3rd character but the 2nd
however
it will all go very wrong if the month does not have 2 characters

22nd jan 2024
1222024
12 22 2024
or
but could be the
1222024
2nd dec 2024
1 22 2024

same number
2 different dates
no way to know what to do

then if the same issue for time - then anything could happen
112024111
1 jan 2024 01:01:01

20th Nov 2411 and time would be who knows

do you have a few example

 

[Nu2Java]

Ok, I see what you are saying there. I did look at some other data and noticed however it was formatted originally, they have the same as I gave you, but with single digit month (2 instead of 02). The rest of the data is the same.

 

[ETAF]

so is the day 01 02 etc and the time 6 characters so 010101 for 1 hr 1 min 1 sec

if its just the first character - then i suspect its because its been changed to a number by excel

02012024111200
excel would remove the first 0 and change to
2012024111200

and format as a number

if so then we could change the formula and use an IF
so
=DATEVALUE(LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&MID(A1,5,4))+TIMEVALUE(MID(A1,9,2)&":"&MID(A1,11,2)&":"&RIGHT(A1,2))
=IF( Len(a1)=14 , DATEVALUE(LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&MID(A1,5,4))+TIMEVALUE(MID(A1,9,2)&":"&MID(A1,11,2)&":"&RIGHT(A1,2)),
DATEVALUE(LEFT(A1,1)&"/"&MID(A1,2,2)&"/"&MID(A1,4,4))+TIMEVALUE(MID(A1,8,2)&":"&MID(A1,10,2)&":"&RIGHT(A1,2)) )

in this format
02012024111200
14 characters LEN()
and
2012024111200
is 13 characters - BUT in the IF - i have just assumed if NOT 14 then its 13

 

[Nu2Java]

You nailed it. That seems to account for the variations. Thank you so much ETAF

 

[ETAF]

you are welcome, glad to have helped

 

[Scott Huish]

=--TEXT(A1,"00-00-0000 00\:00\:00")

Format as mm/dd/yyyy h:mm:ss AM/PM

 

[GraH - Guido]

Something else that might be easier to maintain or to read even.

=LET(
v_Check;LEN(A3);
v_Str;IF(v_Check=14;A3;"0"&A3);
v_SplitUnits;MID(A3;SEQUENCE(LEN(v_Str));1);
v_Units;BYCOL(WRAPCOLS(v_SplitUnits;2);LAMBDA(c;TEXTJOIN("";TRUE;c)));
v_dte;DATE(INDEX(v_Units;3)*100+INDEX(v_Units;4);INDEX(v_Units;1);INDEX(v_Units;;2));
v_tme;TIMEVALUE(" "&TEXTJOIN(":";TRUE;TAKE(v_Units;;-3)));
HSTACK(v_dte;v_tme))

 

[GraH - Guido]

=--TEXT(A1,"00-00-0000 00\:00\:00")

Format as mm/dd/yyyy h:mm:ss AM/PM

That's a winner!
I've seen the trick before, but I never remember it.

I remark, if one is using EU-format, the formula breaks because when the value is impossible, e.g. the month 15 does not exist.

 

[Nu2Java]

This is great info. Thanks to everyone else who gave solutions, I will try them all out.

 

[sterycop]

Hmm this is very strange. I cannot get it to work.