I have done this as I explained in the question.
The format cammand does not work on all the cells.
When I go to the offending cell individually and format it, it does not work.
The cell reads 6/3/09. I format it to read 06/03/09 and it does not change the cell
Some cells in the range accept month day year others you have to enter day month year
Hi
lifesaver asked me to try to assist you with this question.
I am assuming that you are having this issue only on this spreadsheet. If so can you try resetting the formatting of the worksheet by selecting the entire sheet and then clicking Format > Cells > Numbers , then choose 'General' .(to select the entire sheet click on the little square in the upper left corner of sheet just above row numbers).
Next click on the cell that you want to format and type 5/28/09 and hit Enter. Then apply the date format that you want. Let me know what is displayed when you do that.
Tried a completely new spreadsheet
Formatted 03/06/09
typed 6/12/09 got 12/6/09
Tried you suggestion on the old sheet The cells do not format consistently Some have number some have date in different formats
hmm...I did not suggest that you do any of that!
Can you please just tell me the exact result that you got in that cell after following my instructions, especially after typing 5/28/09. Thanks.
After typing 5/28/09 say in cell A1, please type =A1+1 in cell B1. Do you get a #VALUE! error?
That is basically what I wanted to know.
I think it would be easier to upload your file at http://www.rapidshare.com/ then when it finishes uploading you will get a "download link" . Highlight that link then copy it and paste it in your reply box here.
Let me know if you face any difficulties.
Did you upload the file. If so you need to send me the link so that I can access that file.
Ok since you are getting a #VALUE! error it means that the entry is in A1 is not being understood by Excel as a correct date. If you type 28/5/09 in A1 i instead of 5/28/09 and you do not get the VALUE error in cell B1, then the problem is in your global computer date settings (not just in Excel). Here is how to correct that it (the exact procedure depends on your operating system).
In Windows XP, close all applications and files, click Start > Control Panel then open 'Regional and Language Options'.then click on the tab 'Regional Option', and in the drop down list at the top change the language from "English (United States)" to "English (United Kingdom)" . Click Apply. Then reboot your computer.
Now open that Excel file and let me know if the problem gets resolved or not.
Hope this helps. Let me know if you have any questions.
BS Degree in Engineering
20 years experience in Excel programming and various major applications.
That worked Thank you
Glad to hear that. Thank you very much for the accept and bonus.