Format Cells as Date not changing cell data

[craigo298er]

I downloaded a spreadsheet and amongst other data is a Due Date column. I'd like to change it from MM-DD-YYYY to DD-Mmm-YYYY. Usually it is simple enough...just highlight the cells>Format Cells>Date>14-Mar-12, except doesn't work. The cells only have the ##-##-####, no other spaces or text in the box.

I can go to each cell and type the date I want (for example 28 June 2020) and it then changes to the correct format, but it isn't changing it unless I change each cell text. I don't really feel like doing that to almost a thousand cells. Any idea?

 

[Rick Rothstein]

yeah, I caught it after I posted - see new link here. put in a LEN() as well for fun
https://drive.google.com/file/d/1B2_ph0ML_x7w_iftL6cvZgWeIxoPYtHP/view

Your dates with slashes appear to be text (because their length is 10) without the extra characters I thought might be in there. The two non-slash dates are true excel dates (length is 5 which is how long the underlying integer value would be for a real date). Unfortunately, I am not sure what to tell you as the procedure I outlined in Message #2 should have worked for such values. Is there any chance you can post a copy of your actual workbook to DropBox for us (delete sensitive info like names, addresses, code numbers of any kind as I only care about your date column being untouched)?

 

[craigo298er]

Your dates with slashes appear to be text (because their length is 10) without the extra characters I thought might be in there. The two non-slash dates are true excel dates (length is 5 which is how long the underlying integer value would be for a real date). Unfortunately, I am not sure what to tell you as the procedure I outlined in Message #2 should have worked for such values. Is there any chance you can post a copy of your actual workbook to DropBox for us (delete sensitive info like names, addresses, code numbers of any kind as I only care about your date column being untouched)?

https://www.dropbox.com/s/jmjb6zusletxmv5/ABS Date table.xlsx?dl=0

 

[Fluff]

If you local settings are British/european.
Select the column with the dates > Data tab > text to columns > delimited > Clear all checkboxes > select MDY > Ok

 

[craigo298er]

If you local settings are British/european.
Select the column with the dates > Data tab > text to columns > delimited > Clear all checkboxes > select MDY > Ok

no, didn't do it
I've tried replacing all the /'s with -'s, or .'s, nothing works to get them to be dates. I have 8 other tabs that all do the same with three dates (due date, done date, and previous done date), but none of them are linked or have formulas. I just downloaded the data from the provider by clicking the website's download button for Excel file

 

[Fluff]

Did you select the MDY Option in Date? As that worked for me.

 

[craigo298er]

Did you select the MDY Option in Date? As that worked for me.

it didn't last night for me, but worked just now. thank you very much
odd, but my coworker did say IS/IT dept pushed a Windows update today throughout the fleet, so maybe that had something to do with it.

 

[Fluff]

Glad it's sorted & thanks for the feedback