Excel 2010日期格式不适用于某些单元格，但适用于其他单元格 [英] Excel 2010 date formatting not working on some cells but on others
问题描述
在Excel 2010中，我有一系列看起来像日期的东西。当我尝试格式化它们时，只有一些已更改，而其他则保持不变。为了清楚起见，右对齐的日期更改没有，左对齐的日期没有更改。 （请参阅 M列），我可以将右对齐的日期格式化为其他任何形式，例如百分比，常规等。即使右对齐的日期也已选择，它们也会更改，而左对齐的日期将保持不变。
我可以编辑左对齐的单元格，即更改我看到的值。
从外观上看，我认为左对齐的日期是文本。我读到文字在编辑栏前带有撇号，虽然不是，但仍然可以是文字。我不确定。
我尝试过的操作：
1）使用 = DATEVALUE。这是行不通的。
2）格式化日期，将单元格复制到记事本中，然后再复制回去。这行不通。
3）使用文本到列按钮。分隔选项，然后是下一步，未选中按钮，然后是下一步，然后选择 DMY并点击完成按钮。这是行不通的。
从我在 L列中输入的示例可以看出，我的公式是正确的。我手动输入了这些日期，它们按预期方式工作。 M列是其中之一。它显示#VALUE！错误。图片已附上。
任何人都可以提供的帮助倍受赞赏。我不确定还有什么尝试。
我想更改 L列。我希望它们全部都是日期值。
感谢您的帮助。
我已经用图像图片更新了问题。我已经用日期，货币0小数和文本格式化了相同的单元格。您可以看到右侧的单元格如何变形为与其日期格式无关的（显示）。这就是问题。我不确定为什么会这样。有什么想法吗？
太长，无法容纳在注释中。左对齐的是字符串，可以将其处理为Excel可以识别的日期/时间序列号（如19/19/2013这样的废话）。目前，它们是美国格式（MDY），月（在您的配置中）超过了 12 。但是，真正的问题是Excel 已识别的，因为它已经为Excel创建了日期/时间序列号-但不是正确的序列号。因此 05/02/2013 它被认为是2月5日，而如果以相同的来源和其他日期作为其样式，则实际上应该代表5月2日。 / p>
可以将文本和错误日期都转换为常规日期序列号，但比退回舞台并用正确的方式填充 Excel要多'数据开始。例如，首先将其正确键入，在源中重新排列格式，或通过从MDY到DMY的适当转换来导入（例如，文本导入向导中的第3步，共3步）。
编辑评论
是由您自己决定，但要针对如何将左对齐值（字符串）修改为日期？：
= DATE（RIGHT（A4,4），LEFT（A4，FIND（ /，A4,2）-1），MID（A4，FIND（ /，A4）+1 ，2））
对于我只关注年份部分，也许（这是安全的'）：
= IFERROR（YEAR（A4），RIGHT（A4,4））+ 0
此外，还要解释奇数四位数字
以最后一个为例（ 02/08/2012 ），它表示2月8日， 2012年，在Excel的1900年日期系统中，日期序列号为40947（大致自1900年1月1日以来的天数，即计量表开始的天数）。最右边的四位数是 0947 。
切换到第一个示例（ 02/05/2013 ），表示2013年2月5日。比上一个示例晚了整整一年（363天）。忽略数千，947（从上面）+ 363 = 1310 。
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?
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.
这篇关于Excel 2010日期格式不适用于某些单元格，但适用于其他单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
