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

[pickslides]

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]

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]

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 :)]

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]

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 :)]

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]

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

Thanks for everyone's help!

[kimudao]

Hi
Bebo021999

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

[AliGW]

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