SOLVED

Struggling with date format

%3CLINGO-SUB%20id%3D%22lingo-sub-2267706%22%20slang%3D%22en-US%22%3EStruggling%20with%20date%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2267706%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20struggling%20to%20change%20the%20date%20format.%3C%2FP%3E%3CTABLE%20width%3D%22161%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22161%22%3E31%2F03%2F2021%2023%3A48%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3Bto%2031%2F3%2F2021%2011%3A48%3A00%20PM.%3C%2FP%3E%3CP%3ENo%20custom%20format%20Is%20working.%20Also%20after%20that%2C%20I.%26nbsp%3B%20have%20to%20extract%20the%20date%20by%20%3DYear(value)%3C%2FP%3E%3CP%3ECan%20somebody%20please%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2267706%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

Hi All,

 

I am struggling to change the date format.

31/03/2021 23:48

 to 31/3/2021 11:48:00 PM.

No custom format Is working. Also after that, I.  have to extract the date by =Year(value)

Can somebody please help

3 Replies
best response confirmed by Debasmita23 (New Contributor)
Solution

@Debasmita23 When you imported the dates, Excel did not recognise 31/03 as a valid date. On the other hand, 04/01 was recognised, it was "translated" to April 1, where the data probably represents 4 January. Hence, column A became a mix of valid dates (though wrong) and texts.

 

I used "text-to-comlumns" (on the Data ribbon) to fix this so that a custom format can be applied as desired. See attached, and check if this is indeed what you want/need.

Hi Riny,

Thank you so much for this. This really helps.

@Debasmita23 Glad it worked out!