Why is Excel changing my date display value, and h

Question

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 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 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 "YYYYMM" 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 1

I think it comes from the way Excel stores dates. They are the number of days since 01-01-1900. Stored dates in Excel

When extracting them, they are looking at your localized setting for the format. Any formatting you want to keep needs to be after or while retrieving them.

Answer 2

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.

Answer 3

It will translate any string to dateTime when you set the Excel field with a date format.

use:

monthYearCell.NumberFormat = "@";

then use:

monthYearCell.Value = GetMMMYYFromYYYYMM(MonthYear);