Why are these MMM YY values displayed as YY-MMM in my Excel spreadsheet?

Question

0.00/5 (No votes)

See more:

I need 13 cells containing three-character months followed by a space and then a two-character year, such as:

Apr 14
May 14
...
Jan 15
...
Apr 15

Getting the beginning (and ending) month and the ending year from the user, I wrote this function to store all the vals in a generic list:

C#

    private List<string> GetMonthAndTruncatedYears(string monthBeginAndEnd, string endYear)
    {
        const int MONTHS_TO_ADD = 13;

        String endYear2Digit = (Convert.ToInt32(endYear)).ToString();
        endYear2Digit = endYear2Digit.Substring(2);
        String beginYear2Digit = (Convert.ToInt32(endYear) - 1).ToString();
        beginYear2Digit = beginYear2Digit.Substring(2);

        String yearToUse = beginYear2Digit;

        List<String> listMonthsYears = new List<string>();
        listMonthsYears.Add(String.Format("{0} {1}", monthBeginAndEnd, yearToUse));
        int valsAdded = 1;
        String curMonth = monthBeginAndEnd;
        while (valsAdded < MONTHS_TO_ADD)
        {
            curMonth = GetMonthStr(curMonth, 1);
            if (curMonth.Equals("Jan"))
            {
                yearToUse = endYear2Digit;
            }
            listMonthsYears.Add(String.Format("{0} {1}", curMonth, yearToUse));
            valsAdded++;
        }
        return listMonthsYears;
    }

    private string GetMonthStr(string beginMonth, int offset)
    {
        int curIndex = UsageRptConstsAndUtils.months.IndexOf(beginMonth);
        if (curIndex == 11)
        {
            curIndex = -1;
        }
        return UsageRptConstsAndUtils.months[curIndex + offset];
    }

public static List<String> months = new List<String>
        {
            "Jan",
            "Feb",
            "Mar",
            "Apr",
            "May",
            "Jun",
            "Jul",
            "Aug",
            "Sep",
            "Oct",
            "Nov",
            "Dec"
        };

The values are stored as desired in the generic list.

I am adding the values to the cells as a string:

C#

_xlSheet.Cells["7", "C"] = monthsTruncatedYears[0];

However, although I expect the items to display raw/verbatim, instead they display in the Excel cells as "14-Apr" etc. Why? Who told them to change format?

NOte: I am using Microsoft.Office.Interop.Excel, v2.0.50727

So how can I tell Excel to leave the formatting be?

Posted 3-Nov-15 5:31am

B. Clay Shannon

Updated 3-Nov-15 5:54am

v2

Add a Solution

Comments

Richard MacCutchan 3-Nov-15 12:04pm

Why are you converting strings to integers just so you can convert them back to strings?
String endYear2Digit = (Convert.ToInt32(endYear)).ToString();
You need to add the formatting information to the cells if you don't want Excel to mess with it. Not sure exactly how but it will be in the interop documentation.

B. Clay Shannon 3-Nov-15 12:08pm

I temporarily convert to int to do the math (subtract 1).

3 solutions

Solution 2

You need to know that Excel is automatically interpreting all string entered.

In Excel
To force Excel to keep the exactly what you typed, you need a "'" as first char
type 08 will display 8
type '08 will display 08

from C# or VBA
Store "08" in a cell and Excel will display 8
Store "'08" in a cell and Excel will display 08

Quote:
Why are these MMM YY values displayed as YY-MMM in my Excel spreadsheet?

so the answer is simple:
Your program enters "JAN 15", excel interpret it as a date (the 1st of the month) and then apply current formatting for a date.
to prevent this,
- You can set the cell format according to your needs.
- You can tell Excel that the value is not to be interpreted by adding a leading mark before the value "'JAN 15".

Posted 3-Nov-15 7:29am

Patrice T

Solution 3

This is what worked, thanks to Andy and Jochen especially:

C#

Microsoft.Office.Interop.Excel.Range monthYearCells = _xlSheet.Range[_xlSheet.Cells[7, 3], _xlSheet.Cells[7, 15]];
monthYearCells.NumberFormat = "@";

Posted 3-Nov-15 8:01am

B. Clay Shannon

Comments

Patrice T 3-Nov-15 14:39pm

You should close the question if solved.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andy Lanng · Accepted Answer · 2015-11-03T05:43:00

Solution 1

Excel is a funny bunny when it comes to dates. It will always store the date as an int. If you tell it to store "Apr 14" it will try to parse that as a date and will always assume the number to be the day (at least before 2032 it will), as that provides the most precision.

Because it will always store a date as an integer (in days), it will need to know what day of the month to use. You will have to tell it "01 apr 2014" for eg.

It will not inherit the formatting of the item you date item you give it. It will try to "infer" this, but it can't from 'MMM YY' because it must assume that that is 'MMM-DD' (hence the odd format)

So - Two Options. add the date as a string and format the cell as text, or add the date as a full date (i.e. dd mmm yyyy) and add custom formatting to the column.

The methods used to format columns depends of what Office APi you're using

Posted 3-Nov-15 5:43am

Andy Lanng

Comments

B. Clay Shannon 3-Nov-15 11:52am

I am adding as a string:
_xlSheet.Cells["7", "C"] = monthsTruncatedYears[0];
What else do I need to do - how do I "format the cell as text"? I would think it would do that by default...
I am using Microsoft.Office.Interop.Excel, v2.0.50727

Andy Lanng 3-Nov-15 11:56am

You are adding as a string but Excel thinks it know better (it rarely does >_<)

Try including this:

_xlSheet.Cells["7", "C"].NumberFormat = "0"; //force as text
_xlSheet.Cells["7", "C"] = monthsTruncatedYears[0];

Or you can format the whole column first:
_xlSheet.Range["C:C"].NumberFormat = "0" //is that how you select a column range? I can't remember

Let me know how it goes

B. Clay Shannon 3-Nov-15 12:00pm

Thanks, Andy, but "NumberFormat" is not available; the only thing Intellisense offers after "_xlSheet.Cells["7", "C"]." are: ToString, Equals, GetHashCode, and GetType. And if append ".ToString()" to the assignment, it greys out, so is apparently redundant...

Andy Lanng 3-Nov-15 12:17pm

Ah - It must only apply to cellrange.
try _xlSheet.Range["C:C"].NumberFormat = "0"; this will format the whole column (or should).
Let me know

B. Clay Shannon 3-Nov-15 12:24pm

I tried this:

var firstMonthCellRng = (MSExcel.Excel.Range)_xlSheet.Cells[7, 3];
firstMonthCellRng.NumberFormat = "0";
_xlSheet.Cells["7", "C"] = monthsTruncatedYears[0];

(I don't want the whole column formatted, these are column headers that only need several cells on one row formatted).

Now, instead of displaying "14-Apr" when it should be "Apr 14" it displays "42108" Was zum Teufel ist hier los?!?

Andy Lanng 3-Nov-15 12:28pm

Hmm - running out of ideas. try this?
https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.namedrange.autoformat.aspx

B. Clay Shannon 3-Nov-15 13:00pm

Thanks for your help, Andy; "Tools" is not recognized in my environment. There's no rush on this; it's just a cosmetic issue.

Jochen Arndt 3-Nov-15 13:28pm

Andy remembered the wrong code. The NumberFormat "0" is for integers. To use text format pass "@".

B. Clay Shannon 3-Nov-15 13:43pm

Yep, that's all it needed, thanks Jochen!

So this works:

var firstMonthCellRng = (MSExcel.Excel.Range)_xlSheet.Cells[7, 3];
firstMonthCellRng.NumberFormat = "@";
_xlSheet.Cells["7", "C"] = monthsTruncatedYears[0];

Andy Lanng 4-Nov-15 4:17am

Glad you got it working

Andy Lanng 4-Nov-15 4:16am

Thanks for your note ^_^