Excel 2010 日期格式不适用于某些单元格但适用于其他单元格 [英] Excel 2010 date formatting not working on some cells but on others

问题描述

在 Excel 2010 中，我有一系列看起来像日期的内容.当我尝试格式化它们时，只有一些改变了，其他的保持不变.需要明确的是，右对齐的日期"会发生变化，而左对齐的日期"不会发生变化.(请参阅M 列")我可以将正确对齐的日期格式化为其他任何内容，例如百分比、一般等.右对齐的日期发生变化，左对齐的日期保持不变，即使它们也被选中.

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.

我尝试过的:

1) 使用=DATEVALUE".这不起作用.2)格式化为日期，将单元格复制到记事本并将它们复制回来.这不起作用.3) 使用文本到列"按钮.分隔选项，然后下一步，取消选中按钮，然后下一步，然后选择DMY"并点击完成按钮.这不起作用.

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.

我的公式是正确的，正如您在L 列"中输入的示例所见.我手动输入了这些日期，它们按预期工作.'列M'是感兴趣的.它显示#VALUE！错误.附上图片.

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.

重申L 列"是我想要更改的那个.我希望它们都是日期值.

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

感谢您的帮助.

我已经用图片的图片更新了问题.我已经用日期、货币 0 十进制和文本格式化了相同的单元格.您可以看到右侧的单元格如何变成与其日期格式无关(看起来)的不同内容.这就是问题.我不确定为什么会这样.有什么想法吗?

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?

推荐答案

评论太长.左对齐的是字符串，可以将其处理为 Excel 识别的日期/时间序列号(而不是像 2013 年 19 月 19 日这样的废话).目前它们是美国格式 (MDY)，并且月份"(在您的配置中)超过 12.然而，真正的问题是 Excel 已经 识别的那些，因为它已经为这些创建了日期/时间序列号 - 但不是正确的.所以 05/02/2013 它认为是 2 月 5 日，而如果来自与其他日期相同的来源和样式，它实际上应该代表 5 月 2 日.

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.

将文本和错误"日期都转换为常规"日期序列号是可能的，但比退一步并以正确"数据开始提供"Excel 要做的工作要多得多.例如，首先将其键入正确"，重新排列源中的格式，或通过从 MDY 到 DMY 的适当转换将其导入(例如，文本导入向导中的第 3 步，共 3 步).

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).

编辑重新评论

由您自己决定 但对于我如何将‘左对齐’值(字符串)修改为日期?":

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

<小时>

另外，解释奇数"四位数值

以最后一个 (02/08/2012) 为例，它代表 2012 年 2 月 8 日，在 Excel 的 1900 日期系统中，日期序列号为 40947(大约 自 1900 年 1 月 1 日以来的天数，当仪表开始"时).最右边的四位数字是0947.

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.

切换到第一个示例 (02/05/2013)，它代表 2013 年 2 月 5 日.比上一个示例晚了将近整整一年(363 天).忽略千位，947(从上面算起)+ 363 = 1310.

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屋！