How to change date format from 201904 to Apr-19 in excel

Question

The date in Excel is formatted as 2019-04. I want it changed to April 19, please. Despite the fact that I have a custom format of mmm-yy, it is transforming it to Apr-52 rather than Apr-19.

Answer 1

This 201904 is recognised as a number by Excel.

In Excel, dates are represented by numbers that start counting on "Day 1" in 1900.

The outcome of converting a number (day count) to a date is 2452 10 15 if you use the format "yyyy mm dd," whereas "mmm-yy" eliminates the first two digits from the years and returns the year you are now viewing, which is 52.

In this situation, you must create a date that Excel can recognise, but bear in mind that you must add the day because Excel only accepts dates that are complete (year, month, and day).

To extract the year, use LEFT, and the month, use RIGHT. Date in Excel can be created by adding any day (in this example, day 1). To achieve your desired outcome, format it as "mm-yy":

=DATE(LEFT(A1,4),RIGHT(A1,2),1)