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

0 votes

I have a different date format(YYYY.MM.DD and MM/DD/YYYY) in my date column in Excel. As a final result, I want one date format ie MM/DD/YYYY. What is the easiest way to achieve it?

Here is an image of my data.on Right-click and Format cell it looks like the below.

enter image description here

The final output I want to like is this. I have tried the Text to Columns option as well but it didn't work.

Dec 24, 2022 in Others by Kithuzzz
• 38,000 points
1,435 views

1 answer to this question.

0 votes

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 to B2.

Then copy B2 downward:

enter image description here

The formula converts any text values in column A into true Excel dates in column B.

answered Dec 24, 2022 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

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

This 201904 is recognised as a number ...READ MORE

answered Mar 31, 2023 in Others by Kithuzzz
• 38,000 points
494 views
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,632 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
794 views
0 votes
1 answer

How to subtract dates in Excel with different date formats?

You can use dates in any computation ...READ MORE

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

Date difference: different results in Excel vs. Python

To calculate the date difference, we can ...READ MORE

answered Feb 23, 2022 in Database by gaurav
• 23,260 points
1,145 views
0 votes
1 answer

IF formula to compare a date with current date and return result

You can enter the following formula in ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
• 63,600 points
1,042 views
0 votes
1 answer

Excel formula to check date within this week and last week

Assuming the first date in A2 try this formula ...READ MORE

answered Sep 29, 2022 in Others by narikkadan
• 63,600 points
1,967 views
0 votes
1 answer

Excel Formula calculating the starting date of a given calendar week

The number of days to take into ...READ MORE

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

Excel: How to merge two columns into one (from different sheets or separated columns)

This equation is completely adjustable. Your two ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,600 points
875 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,715 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