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,420 points
225 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,010 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,288 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,420 points
839 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,420 points
5,350 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,420 points
420 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
876 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,090 points
813 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,090 points
4,063 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
3,789 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,010 points
1,128 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,010 points
363 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