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]

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?

Select the entire column, bring up the Text To Columns dialog box (Data tab, Data Tools panel, Text to Columns button) and click the Finish button as soon as the dialog box appears. If I am right, you should now be able to format the cells the way you want.

 

[craigo298er]

Select the entire column, bring up the Text To Columns dialog box (Data tab, Data Tools panel, Text to Columns button) and click the Finish button as soon as the dialog box appears. If I am right, you should now be able to format the cells the way you want.

unfortunately that does not do anything

 

[Rick Rothstein]

unfortunately that does not do anything

Then I think you have other hidden characters in the cells along with your dates. My guess is you copy/pasted the dates from a website and picked up some trailing non-breaking space characters (ASCII 160) along the way. Assuming cell A1 contains one of your dates, what does this formula display...

=CODE(RIGHT(A1))

 

[craigo298er]

Then I think you have other hidden characters in the cells along with your dates. My guess is you copy/pasted the dates from a website and picked up some trailing non-breaking space characters (ASCII 160) along the way. Assuming cell A1 contains one of your dates, what does this formula display...

=CODE(RIGHT(A1))

CODE shows 69 (changed to H1 see link: https://drive.google.com/open?id=1fR9GVKfrX2WYm7jUHP9YB-YuolCluVhT)

In cells H2 & H3 I typed 28June2020 and it auto changed to the format I want

 

[jtakw]

Hi,

Why are you checking H1? H1 is your Header, you should be checking H2 with Rick's suggestion.

 

[craigo298er]

Then I think you have other hidden characters in the cells along with your dates. My guess is you copy/pasted the dates from a website and picked up some trailing non-breaking space characters (ASCII 160) along the way. Assuming cell A1 contains one of your dates, what does this formula display...

=CODE(RIGHT(A1))

sorry - 57 as H2...wrong cell and changed H4 & H5
https://drive.google.com/open?id=1B2_ph0ML_x7w_iftL6cvZgWeIxoPYtHP

 

[craigo298er]

Hi,

Why are you checking H1? H1 is your Header, you should be checking H2 with Rick's suggestion.

yeah, I caught myself - see above

 

[Rick Rothstein]

CODE shows 69 (changed to H1 see link: https://drive.google.com/open?id=1fR9GVKfrX2WYm7jUHP9YB-YuolCluVhT)

H1 contains your header text (hence the ASCII 69 which is the letter "E").... change the H1 to H2 (your first date cell) in the CODE formula and tell me what it reports.

 

[craigo298er]

H1 contains your header text (hence the ASCII 69 which is the letter "E").... change the H1 to H2 (your first date cell) in the CODE formula and tell me what it reports.

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