Change date format from mm/dd/yy to dd/mm/yyyy

Hi,
I've been searching to convert date format automatically from mm/dd/yy to dd/mm/yyyy. Any help on this is really appreciated.

it works but only for dates after 11/13/19, but before that it gives #value.

Please see attached file.

I have a formula that works to convert mm/dd/yyyy to dd/mm/yyyy. But when excel got value to 11/13/19 then it gets confused.

Formula:

Please Login or Register  to view this content.

Thats basically two formulas one for mm/dd/yyyy and second for mm/dd/yy. I need one formula for both types.


Thanks

Its a pain, but the below as long as all your dates are after 2000
=IF(ISNUMBER(A2),DATE(YEAR(A2),DAY(A2),MONTH(A2)),DATE(2000+RIGHT(A2,2),LEFT(A1,2),MID(A2,4,2)))

your value, if it is a number (date) just needs the month and day swapping. otherwise when it is text, you create the date as shown, but need to add 2000 to the year

Thanks! but I'm confused why is Cell value C13 is different??

See attached file

Because I am not awake!
=IF(ISNUMBER(A2),DATE(YEAR(A2),DAY(A2),MONTH(A2)),DATE(2000+RIGHT(A2,2),LEFT(A2,2),MID(A2,4,2)))

try the above

WOW! Great! and Thanks. It only works for years above 2000. Right?

yes
your problem is with 2 digit dates asigning them to a year, we have to guess which 4 digit year you mean. the below may be a work around if needed

=IF(ISNUMBER(A2),DATE(YEAR(A2),DAY(A2),MONTH(A2)),DATE(IF(RIGHT(A2,2)<50,2000,1900)+RIGHT(A2,2),LEFT(A2,2),MID(A2,4,2)))

you could refine it as above, where if the year is <50 its 2049 above 50 1950 etc change the red for a number that suites your purposes

Hi Rahat,

I think you may be looking for a complicated solution. Hopefully what I suggest below is easier to user.

1) (not mandatory, but will help double check you get the desired result) - Format the date cells to Format dd-mmm-yy. It does give you a peculiar result initially, but ignore it.
2) Highligh the date cells
3) On the Data Ribbon, select Text to Columns
4) Choose Fixed Width, and click Next
5) DO NOT choose to split the dates, just leave as is. JUST CLICK NEXT
6) Select the Date Button and in the drop down select MDY
7) Click OK and it should drop ou the dates to the format you want.

Hope that Helps
Green Aardvard

Nice and easy solution but when I copy data from another source it gives same formatting and I have to manually to it again. I think the formula solution given by davsth is fine. Thanks for your valuable reply.