SOLVED

Date Formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-2629246%22%20slang%3D%22en-US%22%3EDate%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2629246%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20data%20in%20which%20data%20is%20represented%20in%20the%20following%20format%20%3A%3C%2FP%3E%3CTABLE%20width%3D%22206px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22205px%22%3E1st%20Aug%202021%20%7C%208%3A57%20pm%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20change%20this%20into%2008%2F01%2F2021%208%3A57%3A00.%3C%2FP%3E%3CP%3EI%20have%20tried%20using%20text%20to%20columns%2C%20custom%20data%20format%20but%20no%20success.%20Can%20someone%20provide%20any%20inputs%20on%20how%20I%20can%20achieve%20this%20%3F%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2629246%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2629311%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2629311%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1124346%22%20target%3D%22_blank%22%3E%40sp3124%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20it's%20OK%20to%20use%20a%20formula%20to%20return%20a%20date%2Ftime%20value%20in%20another%20cell%3A%3C%2FP%3E%0A%3CP%3EWith%20a%20text%20value%20in%20A2%2C%20enter%20the%20following%20formula%20in%20B2%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2%2C%22st%22%2C%22%22)%2C%22nd%22%2C%22%22)%2C%22rd%22%2C%22%22)%2C%22th%22%2C%22%22)%2C%22%7C%22%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EFormat%20B2%20with%20your%20preferred%20date%2Ftime%20format%2C%20then%20fill%20down.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20to%20convert%20the%20value%20in%20the%20cell%20itself%2C%20it%20would%20require%20VBA.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2629415%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2629415%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%3CBR%20%2F%3EThis%20worked%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3EI%20am%20curious%20to%20know%20how%20is%20this%20working%20in%20the%20background%20%3F%20Using%20substitute%2C%20I%20am%20able%20to%20remove%20the%20st%2Cnd%20etc%20in%20my%20cell%20but%20how%20am%20I%20able%20to%20convert%20it%20into%20date%20format%20%3F%20I%20was%20not%20able%20to%20do%20it%20before.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2629450%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2629450%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1124346%22%20target%3D%22_blank%22%3E%40sp3124%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2%2C%22st%22%2C%22%22)%2C%22nd%22%2C%22%22)%2C%22rd%22%2C%22%22)%2C%22th%22%2C%22%22)%2C%22%7C%22%2C%22%22)%3C%2FP%3E%0A%3CP%3Eremoves%20the%20vertical%20line%20character%20%22%7C%22%20and%20the%20suffixes%20%22st%22%20etc.%3C%2FP%3E%0A%3CP%3EIn%20your%20example%2C%20this%20results%20in%20the%20text%20string%20%221%20Aug%202021%26nbsp%3B%208%3A57%20pm%22.%20While%20it%20is%20still%20text%2C%20Excel%20can%20recognize%20it%20as%20date%2Btime.%3C%2FP%3E%0A%3CP%3EApplying%20-%20forces%20Excel%20to%20treat%20the%20string%20as%20a%20date%2Btime%2C%20but%20it%20turns%20it%20negative.%20The%20second%20-%20turns%20it%20positive%20again.%3C%2FP%3E%0A%3CP%3EIn%20general%2C%20applying%20--%20is%20a%20convenient%20way%20to%20convert%20a%20string%20that%20represents%20a%20date%20or%20number%20to%20a%20%22real%22%20date%20or%20number.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a data in which data is represented in the following format :

1st Aug 2021 | 8:57 pm

 

I want to change this into 08/01/2021 8:57:00.

I have tried using text to columns, custom data format but no success. Can someone provide any inputs on how I can achieve this ?

Thank you.

4 Replies
best response confirmed by sp3124 (Occasional Contributor)
Solution

@sp3124 

If it's OK to use a formula to return a date/time value in another cell:

With a text value in A2, enter the following formula in B2:

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"st",""),"nd",""),"rd",""),"th",""),"|","")

Format B2 with your preferred date/time format, then fill down.

 

If you want to convert the value in the cell itself, it would require VBA.

@Hans Vogelaar
This worked
I am curious to know how is this working in the background ? Using substitute, I am able to remove the st,nd etc in my cell but how am I able to convert it into date format ? I was not able to do it before.

@sp3124 

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"st",""),"nd",""),"rd",""),"th",""),"|","")

removes the vertical line character "|" and the suffixes "st" etc.

In your example, this results in the text string "1 Aug 2021  8:57 pm". While it is still text, Excel can recognize it as date+time.

Applying - forces Excel to treat the string as a date+time, but it turns it negative. The second - turns it positive again.

In general, applying -- is a convenient way to convert a string that represents a date or number to a "real" date or number.

 

@Hans Vogelaar Understood.
Thank you so much for all the help!