cell formatted as date won't change
I have a column of dates (Jul 2,2009) that I want to format as 7/2/09. I can
change the formatting to anything I want and the cells never change. What's wrong? |
cell formatted as date won't change
Your "dates" might actually be entered as text, and not a true date/number.
In which case, you'll need to use something like =DATEVALUE(A2) to convert the text to a number, and then you can format it as desired. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NEHicks" wrote: I have a column of dates (Jul 2,2009) that I want to format as 7/2/09. I can change the formatting to anything I want and the cells never change. What's wrong? |
cell formatted as date won't change
Two ways to do this
1. Select the range of dates which needs to be corrected. From menu DataText to Columns will populate the 'Convert Text to Columns Wizard' Hit NextNext will take you to Step 3 of 3 of the Wizard. From Column Data format select Date and select the date format in which your data is (MDY).Hit Finish. MSExcel will now convert the dates to the default date format of your computer. 2. Using formula; with date in cell A1 =DATEVALUE(TRIM(MID(LEFT(A1,FIND(",",A1)-1),4,4)) & "-"&LEFT(A1,3)&"-" & RIGHT(A1,4)) If this post helps click Yes --------------- Jacob Skaria "NEHicks" wrote: I have a column of dates (Jul 2,2009) that I want to format as 7/2/09. I can change the formatting to anything I want and the cells never change. What's wrong? |
cell formatted as date won't change
If your dates are as you posted in your message; namely, with no space
between the comma and the year, then Excel won't see that as a date. Try selecting the cells containing your "dates", then click Edit/Replace on the menu bar, type just a comma in the "Find What" field and type a comma followed by a space into the "Replace With" field and then click the Replace All button... if your cells are formatted as General, then all your entries should turn into real dates. -- Rick (MVP - Excel) "NEHicks" wrote in message ... I have a column of dates (Jul 2,2009) that I want to format as 7/2/09. I can change the formatting to anything I want and the cells never change. What's wrong? |
cell formatted as date won't change
On Tuesday, July 21, 2009 at 12:51:01 PM UTC-4, Luke M wrote:
Your "dates" might actually be entered as text, and not a true date/number. In which case, you'll need to use something like =DATEVALUE(A2) to convert the text to a number, and then you can format it as desired. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NEHicks" wrote: I have a column of dates (Jul 2,2009) that I want to format as 7/2/09. I can change the formatting to anything I want and the cells never change. What's wrong? This worked for me! Thank you for saving me hours!!!!!! |
cell formatted as date won't change
Basically, the country locale you are in might be the one.
Go to File - SpreadSheet Settings. Select the correct Locale. Eg, US is mm/dd/yyyy whereas UK is dd/mm/yyyy Then you try again to change the format. Hope this helps. Salim Bashar Singapore. On Wednesday, July 22, 2009 at 12:45:02 AM UTC+8, NEHicks wrote: I have a column of dates (Jul 2,2009) that I want to format as 7/2/09. I can change the formatting to anything I want and the cells never change. What's wrong? |
All times are GMT +1. The time now is 04:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com