Date

[alphadrag]

Hi, how do i convert this "3/2/2022 12:00:00 AM" to "3 Mar 2022" using excel formula?

[#2]

@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.

[#3]

How do i convert the decimals (after T) in "2022-03-02T0.770833333333333:00+08:00" to time?

[#4]

@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.

[#5]

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")

[#6]

@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")

[#7]

Yeap, i got it already. Realised i forgot to wrap that part in the TEXT function. Thanks for your help anyways.