Need to work with dates formatted as mm/dd/yyyy Excel 2016

[ggirf14]

Trying to find a setting to allow Excel to manage the data as dates.
mm/dd/yyyy as in 12/24/2016

Will I be forced to use formula to rebuild the data as dd/mm/yyyy?
Find this is strange from a medical device outputting data in non-standard format.

Thanks

 

[63falcondude]

One simple way would be to select the column, Text to Columns, Next, Next, Date: DMY, Finish. Does that solve your issue?

 

[WarfritLive]

=TEXT(A1,"MM/DD/YY") does what you want. Where A1 is the date you want to format

 

[Dave Patton]

If you regularly want to use the date format as mm/dd/yyyy (12/24/2016),

you can change your computer's regional setting to the setting that you require.

See if setting as USA works for you.

 

[ggirf14]

=TEXT(A1,"MM/DD/YY") does what you want. Where A1 is the date you want to format

No luck with this one, still as txt, cannot calculate dates from this output.

 

[63falcondude]

No luck with this one, still as txt, cannot calculate dates from this output.

Did you try my suggestion? Instead of DMY, use MDY and then format the cells into dd/mm/yyyy.

 

[ggirf14]

.... you can change your computer's regional setting to the setting that you require.

That would not work for the next computer the file will be open from.

Is the formula I was trying to avoid the top contender?

 

[Marcelo Branco]

One simple way would be to select the column, Text to Columns, Next, Next, Date: DMY, Finish. Does that solve your issue?

Hi 63fal

I think it should be
select the column, Data > Text to Columns, Next, Next, Date: MDY, Finish

M.

 

[63falcondude]

Hi 63fal

I think it should be
select the column, Data > Text to Columns, Next, Next, Date: MDY, Finish

M.

You are correct, that was my mistake. I mentioned that in Post #6. Since his data is already in mm/dd/yyyy format as text, he could use the Text to Columns feature (using MDY) to get it into a date and then format it as dd/mm/yyyy.

 

[ggirf14]

Did you try my suggestion?

Yes but Excel 2016 does not have the format option of mm/dd/yyyy, the closest is dd/mm/yyyy
Most likely why the text formula leave the output as txt as it does not recognize the format.