Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!!!!!! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting date from Date and Time formatted cell | Excel Discussion (Misc queries) | |||
When using & and a date-formatted cell in a formula I lose the for | Excel Worksheet Functions | |||
how do i add 1 year to a date formatted cell? | Excel Worksheet Functions | |||
Adding time to date-time formatted cell | Excel Discussion (Misc queries) | |||
Converting 'General' formatted cells to Text formatted cell using. | Excel Worksheet Functions |