Struggling with date format

[Debasmita23]

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

[#2 · Accepted 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.

[#3]

Hi Riny,

Thank you so much for this. This really helps.

[#4]

@Debasmita23 Glad it worked out!