Excel 2010 date formatting not working on some cel

问题:

In Excel 2010, I have a range of what looks like dates. When I attempt to format them only some changed the others remain unchanged. To be clear the right aligned 'dates' change and the left aligned 'dates' do not. (refer to 'Column M') I can format the right aligned dates into anything else such as percentage, general etc. whatever. The right aligned dates change and the left aligned remain unchanged even though they are also selected.

I can edit the left aligned cells i.e. change the value I see.

From looking I thought the left aligned dates were text. I read that text has an apostrophe before it in the formula bar, these do not, but could still be text. I am not sure.

What I have tried:

1) Using '=DATEVALUE'. This does not work. 2) Formatting to date, copying cells to notepad and copying them back. This doesn't work. 3) Using 'Text to Columns' button. Delimited option, then next, buttons unchecked, then next, then choosing 'DMY' and hitting Finish button. This does not work.

My formula is correct as you can see by the example I put in, in 'Column L'. I entered those dates manually and they work as expected. 'Column M' is the one of interest. It displays the #VALUE! error. Image attached.

Any assistance one could provide is greatly appreciated. I'm not sure what else to try.

To reiterate 'Column L' is the one I want to change. I want them all to be date values.

Thank you for your help.

I have updated the question with a picture of the image. I have formatted the same cells with date, currency 0 decimal and to text. You can see how the right sided cells morph into something different unrelated (it appears) to their date format. This is the problem. I am not sure why this is happening. Any thoughts?

回答1:

Too long to fit in a Comment. The left-aligned ones are strings, which can be manipulated into date/time serial numbers recognised by Excel (where not nonsense like 19/19/2013). At the moment they are US format (MDY) and the "months" (in your configuration) exceed 12. The real problem however is the ones Excel has recognised, because it has created date/time serial numbers for these - but not the right ones. So 05/02/2013 it believes to be February 5, whereas if from the same source and hence style as the other dates, it actually is supposed to represent May 2.

Conversion of both text and 'wrong' dates into 'conventional' date serial numbers is possible but much more work than stepping back a stage and 'feeding' Excel with the 'right' data to begin with. For example keying it in 'properly' in the first place, rearranging the format in the source, or importing it with appropriate conversion from MDY to DMY (eg step 3 of 3 in the Text Import Wizard).

---

Edit re comment

Be it upon your own head but for "how I could modify 'left aligned' values (strings) to dates?":

=DATE(RIGHT(A4,4),LEFT(A4,FIND("/",A4,2)-1),MID(A4,FIND("/",A4)+1,2))  

For "I am only interest in the year part" maybe (this is 'safe'):

=IFERROR(YEAR(A4),RIGHT(A4,4))+0

---

Also, to explain the 'odd' four digit values

Taking the last for example (02/08/2012) it is a representation of February 8, 2012, which in Excel's 1900 date system is a date serial number of 40947 (roughly the number of days since 1/1/1900, when the 'meter started'). The four digits furthest to the right are 0947.

Switching to the first example (02/05/2013) that is a representation of February 5, 2013. Almost a full year (363 days) later than the last example. Ignoring the thousands, 947 (from above) + 363 = 1310.

回答2:

I've had the same problem (after formatting, some cells were o.k. while some were not). What worked for me was: 1. Select the entire range of interest. 2. Format the range to the wanted date format (one corresponding to the already existing layout of the dates, i.e. if what you now have is 1/15/14, then use "English US" and choose, for example, "March 4, 2011" format - the point here being that you match the format with the layout you have (MM/DD/YY = MM/DD/YY). 3. Go to "Data", go to "Text to Columns", go "Delimiters", eliminate all delimiters, go next, select date and choose the format corresponding to your selected date format (in my case I chose MDY). Thank you all for your input.

回答3:

Not quite sure what you are asking for exactly, but it seems like you want to reformat a US based date to a European format. If i deciphered your question correctly, then maybe the below will help.

The formula in the cell beneath 'European' is =TEXT(A2,"dd/mm/yyyy")

Review this link for more information: http://office.microsoft.com/en-us/excel-help/text-function-HP010062580.aspx

Can you please elaborate on how the image below does not fit your needs?

回答4:

I've had all exactly the same problems - some fields changing and others not.

Turns out the fields that didn't change had a space after the data (already present when imported into Excel).

I Removed the space and excel instantly changed it into desired format!