B. Clay Shannon B. Clay Shannon - 1 year ago 74
C# Question

Why is Excel changing my date display value, and how can I prevent it from doing that?

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

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");

...called like so:

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

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

I debugged the value of
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
helper method.

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"?

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

monthField.NumberFormat = "MMM yy";

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download