Apr 12 2021 03:56 AM
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
Apr 12 2021 04:09 AM
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.
Apr 12 2021 04:30 AM
Apr 12 2021 04:09 AM
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.