• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel convert/format string back to date & time

N

Nu2Java

Member
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

Code: 
02152023122500
mmddyyyyhhmmss
 
Last edited:
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))
 

Attachments

  • datetime from text -ETAF.xlsx
    9.2 KB · Views: 4
strange
works for me

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

Attachments

  • Screenshot 2024-02-16 at 15.26.02.png
    Screenshot 2024-02-16 at 15.26.02.png
    93.8 KB · Views: 2
365 should work - I'm on 365

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

Attachments

  • datetime from text -ETAF2.xlsx
    9.2 KB · Views: 2
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.
 
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
 

Attachments

  • datetime from text -ETAF3.xlsx
    9.2 KB · Views: 4
Nu2Java said:
I am in the US, so I use MM/DD/YYYY
Click to expand...
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
 
That did the trick... I really need to learn more about this kind of formula. Thanks so much for your help on this :)
 
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
 
Last edited:
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.
 
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
 
Something else that might be easier to maintain or to read even.
Code: 
=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))
 
Scott Huish said:
=--TEXT(A1,"00-00-0000 00\:00\:00")

Format as mm/dd/yyyy h:mm:ss AM/PM
Click to expand...
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.

1708106203607.png
 
Last edited:
You must log in or register to reply here.
Back
Top