为什么Excel更改日期显示值，如何防止这样做？ [英] Why is Excel changing my date display value, and how can I prevent it from doing that?

问题描述

似乎Excel正在为我分配给单元格的值，例如Sep 15，并将其更改为15-Sep

It seems that Excel is taking values I assign to cells, such as "Sep 15" and changing them to "15-Sep"

我有代码应该以我想要的格式分配val（ MMM yy ）：

I have code that should assign the val in the format I want (MMM yy):

internal static string GetMMMYYFromYYYYMM(String YYYYMMVal)
{
    // code from http://stackoverflow.com/questions/40045761/how-can-i-convert-a-string-in-the-format-yyyymm-to-mmmyy
    DateTime intermediateDate = DateTime.ParseExact(YYYYMMVal, "yyyyMM", CultureInfo.InvariantCulture);
    return intermediateDate.ToString("MMM yy");
}

...如此调用：

var monthYearCell = _xlPivotDataSheet.Cells[_lastRowAddedPivotTableData + 1, 4];
monthYearCell.Value2 = GetMMMYYFromYYYYMM(MonthYear);

...但数据仍然被写入/显示为YY-MMM，如15-Sep

...but the data is still being written/displayed as "YY-MMM", such as "15-Sep"

我在直接上面执行的两行之后调试了 monthYearCell 的值，并发现monthYearCell对象的Text属性确实是15-Sep; 应该是9月15日，因为这是从 GetMMMYYFromYYYMM（）帮助方法返回的内容。

I debugged the value of monthYearCell after the two lines directly above execute, and find that the "Text" property of the monthYearCell object is indeed "15-Sep"; it "should" be "Sep 15", as that is what is being returned from the GetMMMYYFromYYYYMM() helper method.

Excel显然（右）将值从Sep 15更改为15-Sep等等。我认为Excel必须在幕后或某事上进行自动校正它让我想起了我曾经看过的一部电影（Reds），当他的编辑改变了他写的内容时，主角走了半场。我经常觉得这样对Word和Excel。我怎么能告诉Excel（假设这是问题），只能离开它 - 我写的是什么，我写了？

Excel is obviously (right?) changing the values from "Sep 15" and such to "15-Sep" and such. I reckon Excel must be "autocorrecting" behind the scenes or something; it reminds me of a movie I saw once ("Reds" maybe?) where the protagonist went semi-ballistic when his editor changed what he wrote. I often feel this way about Word and Excel. How can I tell Excel (assuming this is the problem) to just leave it alone - "What I have written, I have written"?

我确实尝试改变从原始YYYYMM格式的值，我以前想要的方式：

I did try to change the value from the raw "YYYYMM" format to what I want this way previously:

monthField.NumberFormat = "MMM yy";

...但是将201509和201510等值更改为Sep 51对于两个...？！？

...but that changed values such as "201509" and "201510" to "Sep 51" for both...?!?

推荐答案

如果您使用'表示Excel来预先添加值，则应将其视为文本那么它不会尝试将您的值解析为日期。

If you prepend the value with ' to indicate to Excel it should be treated as text then it will not try to parse your value into a Date.

这篇关于为什么Excel更改日期显示值，如何防止这样做？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！