Change mm/dd/yyyy to dd/mm/yyyy

**pickslides** · 02-20-2014, 01:07 AM

I need to change mm/dd/yyyy to dd/mm/yyyy.

Going to format cells and custom Excel thinks the data is already dd/mm/yyyy. The data I have is historical so the date 1/03/2014 has not happened yet so I need to change it to 3/01/2014.

Thanks

MQ...

**NickyC** · 02-20-2014, 01:15 AM

Hi
are the underlying date values correct? sometimes they are not, if data are imported from elsewhere. If Excel thinks your 1 March 2014 is actually 3 January 2014 they you're goung to have to change the date values themselves. Otherwise, you can just change the date format in the cell.

**bebo021999** · 02-20-2014, 01:17 AM

Seems date store as text must be converted to value.

So need to convert day to month and vice versa:

=TEXT(DATEVALUE(C16),"mm/dd/yy")

it populates text value. If value needed:

=--TEXT(DATEVALUE(C16),"mm/dd/yy")

or

=DATEVALUE(TEXT(DATEVALUE(C16),"mm/dd/yy"))

**:) Sixthsense :)** · 02-20-2014, 01:29 AM

Or use Text To Columns Option.

Select the Data Column>>Press Alt+D+E>>Delimited>>Next>>Next>>Column Data Format>>Date>>Select your desired date format in the drop down>>Click Finish

**pickslides** · 02-20-2014, 01:36 AM

Originally Posted by :) Sixthsense :)

Or use Text To Columns Option.

Select the Data Column>>Press Alt+D+E>>Delimited>>Next>>Next>>Column Data Format>>Date>>Select your desired date format in the drop down>>Click Finish

Thx

That is the only solution that seemed to work. Can this be done using formulae?

**:) Sixthsense :)** · 02-20-2014, 03:06 AM

Surely.... But we need a sample workbook with expected result.

Please mention your system Date Setting which you can find in Regional Date/Time Setting under Control Panel

**pickslides** · 02-24-2014, 08:52 PM

I solved this tricky little problem by extracting just the day().

Thanks for everyone's help!

**kimudao** · 11-01-2023, 01:36 AM

Hi
Bebo021999

I tried your formular but shown as #VALUE!
please suggest?
Thanks.

**AliGW** · 11-01-2023, 02:00 AM

Administrative Note:

We are happy to help, however you are not allowed to piggy-back another member's thread, so please open your own with a suitable title and, if you wish, a link back to this thread.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Change mm/dd/yyyy to dd/mm/yyyy

LinkBack

Thread Tools

Display

Change mm/dd/yyyy to dd/mm/yyyy

Re: Change mm/dd/yyyy to dd/mm/yyyy

Re: Change mm/dd/yyyy to dd/mm/yyyy

Re: Change mm/dd/yyyy to dd/mm/yyyy

Re: Change mm/dd/yyyy to dd/mm/yyyy

Re: Change mm/dd/yyyy to dd/mm/yyyy

Re: Change mm/dd/yyyy to dd/mm/yyyy

Re: Change mm/dd/yyyy to dd/mm/yyyy

Re: Change mm/dd/yyyy to dd/mm/yyyy

Thread Information

Users Browsing this Thread

Similar Threads

Change of date format from Excel2010 to Excel2013 (dd/MM/yyyy to dd-MM-yyyy)

Copy+Paste macro dd/mm/yyyy to dd/mm/yyyy and NOT dd/mm/yyyy to mm/dd/yyyy

[SOLVED] how do I change date from mm/dd/yyyy to dd:mm:yyyy format in Excel

[SOLVED] change birthday display from mm/dd/yyyy to HIDE the yyyy?

change date format from dd/mm/yyyy to mm/yyyy

Bookmarks

Bookmarks

Posting Permissions