Apr 24 2022 07:12 PM
Hi, how do i convert this "3/2/2022 12:00:00 AM" to "3 Mar 2022" using excel formula?
Apr 24 2022 09:18 PM
@alphadrag This formula will transform the long date to the desired text format:
=TEXT(A1,"d mmm yyyy")
where A1 contains the date/time. However, I believe that the date in your example will become "3 feb" or "2 mar" but not "3 mar",
If you want to keep the date in A1 but merely display it as "d mm yyyy", you can use a custom format.
Apr 26 2022 08:51 PM
Apr 26 2022 09:59 PM
@alphadrag That could be:
=TEXT(MID(A1,12,FIND(":",A1)-12),"hh:mm")
to create a text that looks like a time, or
=TIMEVALUE(TEXT(MID(A1,12,FIND(":",A1)-12),"hh:mm"))
to create a real time value. Format as Time.
Both ignore the bit at the end though, ":00+08:00", as that is not part of a time you want to extract.
Apr 26 2022 10:04 PM - edited Apr 26 2022 10:41 PM
it is not what i want. What i want is this whole thing here "2022-03-02T0.770833333333333:00+08:00" exactly the same, but the number should be in time form.
The final output should be like: 2022-03-21:30:00+08:00
Currently, this is the formula: =CONCATENATE(TEXT('Inputs by AL'!G9,"yyyy-mm-dd"),"T",'Inputs by AL'!J9,":00+08:00")
Apr 26 2022 10:55 PM
@alphadrag You need to wrap the time portion in its own TEXT function. Something like this:
=CONCATENATE(TEXT('Inputs by AL'!G9,"yyyy-mm-dd"),"T",TEXT('Inputs by AL'!J9,"hh:mm"),":00+08:00")
Apr 26 2022 10:57 PM - edited Apr 26 2022 10:57 PM
Yeap, i got it already. Realised i forgot to wrap that part in the TEXT function. Thanks for your help anyways.