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

0 votes
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.
Mar 31, 2023 in Others by narikkadan
• 63,600 points
502 views

1 answer to this question.

0 votes

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)

enter image description here

answered Mar 31, 2023 by Kithuzzz
• 38,000 points

Related Questions In Others

0 votes
1 answer

Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
1,643 views
0 votes
1 answer

How to change two different date format into single date format in excel

With data in A2, in B2 enter: =IF(ISNUMBER(A2),A2,DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2))) and apply your format ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 63,600 points
1,455 views
0 votes
1 answer

How to change the default number format in Excel?

Excel Tips, Learn Excel \ Raghu R Setting ...READ MORE

answered Dec 17, 2022 in Others by narikkadan
• 63,600 points
6,723 views
0 votes
1 answer

How to subtract date only format "mm/dd" in excel?

I suppose Excel has handled December 21 ...READ MORE

answered Dec 25, 2022 in Others by narikkadan
• 63,600 points
808 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,254 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 58,020 points
1,113 views
0 votes
1 answer

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

answered Oct 15, 2018 in RPA by Priyaj
• 58,020 points
4,391 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
4,279 views
0 votes
1 answer

Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

answered Mar 21, 2023 in Others by Kithuzzz
• 38,000 points
1,791 views
0 votes
1 answer

How to detect value in change in excel using python?

You might keep a txt file with ...READ MORE

answered Mar 24, 2023 in Others by Kithuzzz
• 38,000 points
759 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP