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

 

[MARK858]

Try...

=MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,4)

 

[ggirf14]

.... Text to Columns, .....

I would work but
I'm trying to get a solution where I could dump new data periodically without having to manipulate it every time.

 

[63falcondude]

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.

That is why you use Text to Columns. TTC put it into a date format, then you can convert it from the date format mm/dd/yyyy to dd/mm/yyyy.

Edit: Just read your Post (#12).

 

[Marcelo Branco]

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.

Try my suggestion in post #8

Select the range with dates
Data > Text to Column
Next, Next
check Date and pick MDY
Finish

 

[ggirf14]

Try... =MID(....

Gee all of you are fast, I like this forum.
I guess I will have to go with the formula I was trying to avoid.
Thanks for writing it.
I only needed to add SUM((%Your formula%),0) to force excel to look at it as a number "date"
Learned days ago about coercing excel this way Love doing this to Excel

 

[ggirf14]

Try my suggestion in post #8

Ref: Post #12

Thank you

 

[MARK858]

You just need to add +0 to the formula I posted if it isn't recognising it as a date and format the cell with a date format.