Change Date Format mm/dd/yy to dd/mm/yy

[rubz71]

Hi All,

Need your help with update the date from mm-dd-yy to dd-mm-yy

The dates are set on the date format currently and appearing as 12/31/15 (mm/dd/yy).

I would like the formula to format the date to 31/12/12 (dd/mm/yy).

I have about 1000s of rows, which i would like to simply drag down to update to the new dd/mm/yy format.

Thanks in advance!

[6StringJazzer]

Does your formula include an explicit formatting of the date using the TEXT function? If show, show us the formula. Or does it just calculate a date? If it just calculates a date, change the Cell Format, not the formula. You can use a custom format of "dd-mm-yy". You can just select all the date cells and do this in one shot.

If this isn't clear, attach your file. If your file is too big to attach, make a copy and just include the first few rows.

[rubz71]

Does your formula include an explicit formatting of the date using the TEXT function? If show, show us the formula. Or does it just calculate a date? If it just calculates a date, change the Cell Format, not the formula. You can use a custom format of "dd-mm-yy". You can just select all the date cells and do this in one shot.

If this isn't clear, attach your file. If your file is too big to attach, make a copy and just include the first few rows.

Hi Jeff,

I have the below formula and it only works with a string(TEXT). It doesnt work with the date format.

=DATE(VALUE(RIGHT(A3,4)),VALUE(LEFT(A3,2)),VALUE(MID(A3,4,2)))

This formula will return 31/12/2016 (dd/mm/yyy) from 12/31/2016(mm/dd/yyyy)

Is there any way i can make work from date to date format?

also dd/mm/yy to mm/dd/yy. Thanks, Jeff!

[avk]

Firstly confirm about your system date format, which find you in "Control Panel".
>Go to "Start"
>Click "Control Panel"
>Go to "Regional and Language Option"
>In tab "Regional Option" click on "Customize"
>In "Customize Regional Option", select "Date"
>Look into "Short Date Format"

[6StringJazzer]

Firstly confirm about your system date format, which find you in "Control Panel".
>Go to "Start"
>Click "Control Panel"
>Go to "Regional and Language Option"
>In tab "Regional Option" click on "Customize"
>In "Customize Regional Option", select "Date"
>Look into "Short Date Format"

This is the default date display everywhere in Windows. I doubt that is what the OP wants.

I'm not clear on what you're asking. You have a formula that takes a string and turns it into a date. Then you ask how to do this from date to date format. If you are starting with a date, you don't need a formula to change it, you just need to change the display format, same thing I am describing below:

Your formula is returning an Excel date value. The way it's displayed is based on the format chosen for those cells. You need to follow the advice in my post above.

Select the cells with the dates
Home > Format (in the Cells panel) > Format Cells...
Under Category, select Custom
Under Type, type in this format: dd-mm-yy

[pingpong1]

From the Home Tab go "Format" "Format Cells", in the Number dropdown Tab go to "Date" you should see the format you are looking for there.