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

ggirf14

New Member
Joined
Nov 8, 2016
Messages
40
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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

63falcondude

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
One simple way would be to select the column, Text to Columns, Next, Next, Date: DMY, Finish. Does that solve your issue?
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,562
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Nov 8, 2016
Messages
40

ADVERTISEMENT

=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

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
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.
 
Last edited:

ggirf14

New Member
Joined
Nov 8, 2016
Messages
40

ADVERTISEMENT

.... 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?
 

63falcondude

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,963
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top