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%3CLINGO-SUB%20id%3D%22lingo-sub-2267715%22%20slang%3D%22en-US%22%3ERe%3A%20Struggling%20with%20date%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2267715%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1023515%22%20target%3D%22_blank%22%3E%40Debasmita23%3C%2FA%3E%26nbsp%3BWhen%20you%20imported%20the%20dates%2C%20Excel%20did%20not%20recognise%2031%2F03%20as%20a%20valid%20date.%20On%20the%20other%20hand%2C%2004%2F01%20was%20recognised%2C%20it%20was%20%22translated%22%20to%20April%201%2C%20where%20the%20data%20probably%20represents%204%20January.%20Hence%2C%20column%20A%20became%20a%20mix%20of%20valid%20dates%20(though%20wrong)%20and%20texts.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20used%20%22text-to-comlumns%22%20(on%20the%20Data%20ribbon)%20to%20fix%20this%20so%20that%20a%20custom%20format%20can%20be%20applied%20as%20desired.%20See%20attached%2C%20and%20check%20if%20this%20is%20indeed%20what%20you%20want%2Fneed.%3C%2FP%3E%3C%2FLINGO-BODY%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!