Cell formatted as a date won't allow me to format it

[TigersLions]

Hi Everyone,
I usually just google questions like these and 9 times out of 10, I usually figure out the solution. This one has me stumped. Unlike the solutions I found on the internet which assume the cell is formatted as text, this cell is formatted as a date. Yet, when I try to format it as a short date MM/DD/YYYY like in the other cells, it has no effect on the cell. I have tried custom formats and checking to see if the cell was locked somehow. Not an ideal solution, but I've also tried pasting as a value in a new cell and also doing a datevalue function in a new cell. Nothing is working. I'm also not getting the little sign in the cell that indicates the formatting is different from other cells in that column. They are all showing up as a "date" format. I'm running Excel 2010. Any ideas? Thanks!!!

[/URL] click image upload[/IMG]

 

[TigersLions]

There is only so much I can think of without having access to the issue itself. Perhaps you could download a file to dropbox with only the date column with the issue. Also, did you try the datevalue function as I mentioned before?

OK, let me know if this works.
http://adobe.ly/1lDQIRq

And yes, I did try that datevalue function.

Thanks again!

 

[MARK858]

Seeing as you say Rick's suggestion didn't work what are your windows regional date settings?

Are they dd/mm/yyyy ?

 

[TigersLions]

this is what it says. Let me know if this is what you meant.

[/URL] upload an image[/IMG]

 

[MARK858]

No, regional date and time settings for your computer are in the in control panel in Windows .

It shouldn't be looking at your dates but my computer does the same for your text but putting in the space works with 12 August,2004 because my computer is in dd/mm/yyyy.

 

[TigersLions]

No, regional date and time settings for your computer are in the in control panel in Windows .

It shouldn't be looking at your dates but my computer does the same for your text but putting in the space works with 12 August,2004 because my computer is in dd/mm/yyyy.

Is it this then?

[/URL] image hosting 5mb[/IMG]

 

[Rick Rothstein]

In looking at the file you posted, it appears you did not do what I asked you to do... it looks like your replaced the comma with a space whereas I asked you to put a space after the comma... when I did that (and hit Enter), the cell displayed correctly. Unfortunately, you have other problems with your data. One of your cells contains the word August all by itself.. I think you will have to fix that one individually. Another problem is you have correct dates followed by a Line Feed characters and the same date written out. You can fix all those by using Excel's Replace dialog box. Select the column with your "dates" in them, press CTRL+H to bring up the Replace dialog box, (follow this carefully) put the text cursor in the "Find what" field and press CTRL+J (it won't look like anything happened, but it did), release the CTRL key and then press the asterisk key. Leave the "Replace with" field blank. Next click the "Options" button and make sure the "Match entire cell contents" checkbox has no checkmark in it, then click the "Replace All" button. That should fix nearly all your problems.

 

[MARK858]

Removed because of the edit below

Edit: Rick spotted better than me that you had removed the comma (see how it is laid out in your long date in your regional settings)

 

[Rick Rothstein]

Can you see if it allows you to change the long date to MMMM dd, yyyy , if it allows you then try putting the space in.

If you download the file, I think you will see that neither the cell format nor the regional date setting is the problem. This is simply a case of the OP having text data (that happens to look like an almost correct date format, but not quite) in a cell formatted as DATE. This would be no different than if you type your name in such a formatted cell... the cell would remain in DATE format, but since the value is TEXT (that is, a non-number), the format will not (actually, cannot) be applied to it. If you look at what I posted in Message #16, you will see how to overcome the problems that the OP has in his file.

 

[MARK858]

Hi Rick, I saw your post #16 after I posted mine and edited out the last message and replaced it with

Removed because of the edit below

Edit: Rick spotted better than me that you had removed the comma (see how it is laid out in your long date in your regional settings)

as once I saw your post it was clear what the OP had done

 

[brian.wethington]

In looking at the file you posted, it appears you did not do what I asked you to do... it looks like your replaced the comma with a space whereas I asked you to put a space after the comma... when I did that (and hit Enter), the cell displayed correctly. Unfortunately, you have other problems with your data. One of your cells contains the word August all by itself.. I think you will have to fix that one individually. Another problem is you have correct dates followed by a Line Feed characters and the same date written out. You can fix all those by using Excel's Replace dialog box. Select the column with your "dates" in them, press CTRL+H to bring up the Replace dialog box, (follow this carefully) put the text cursor in the "Find what" field and press CTRL+J (it won't look like anything happened, but it did), release the CTRL key and then press the asterisk key. Leave the "Replace with" field blank. Next click the "Options" button and make sure the "Match entire cell contents" checkbox has no checkmark in it, then click the "Replace All" button. That should fix nearly all your problems.

Slick, now can you tell me what ctrl+j and ctrl+* is?