C# setting format of text correctly in Excel

Question

I have a console app that creates several excel documents and everything works fine expect when I place a string called period which is Jan 19 into the cell A2.

Excel changes it to Jan-19 and when I try to change the format of the field using the code below nothing happens.

period = periodDescription.Substring(0, 3) + " 20" + year;
string numberFormat = "General";
xlWorksheet.Cells[2, 1].NumberFormat = numberFormat;
xlWorksheet.Cells[2, 1] = period;

What am i doing wrong?

Update:

Should add I have tried

string numberFormat = "";
string numberFormat = "MMM yyyy";

Dylan Brams · Answer 1 · 2018-10-25T13:50:52.913

0

The General format will autodetect for dates and format them accordingly. I believe what you want to do is explicitly mark it a string by adding a single quote mark to the beginning of it.

Edit: It might be, depending on your final usage, more appropriate to change the formatting of the output to the "MMM dd". This would leave a date in the cell and format it for display the way you want.

edited Oct 25 '18 at 13:50

answered Oct 25 '18 at 13:47

Dylan Brams

2,089
1
19
36

Do you mean like this? string numberFormat = "'MMM yyyy"; If so that doesnt work. I get 'Jan 19 – Silentbob Oct 25 '18 at 13:50
Well, I assume you want three letter month followed by two digit date. That would be "MMM dd". If you want two digit year, it would be "MMM yy". – Dylan Brams Oct 25 '18 at 13:53
If you want it stored in Excel as a string, explicitly putting "'Jan 19" (Note the single quote) into the cell may be your only option, or you could change the numberFormat in a few different ways as shown. – Dylan Brams Oct 25 '18 at 13:55

score 0 · Answer 2 · answered Oct 25 '18 at 14:36

0

The post here:

Changing excel format

Answer the question with:

string numberFormat = "@";

answered Oct 25 '18 at 14:36

Silentbob

2,805
7
38
70

score 0 · Answer 3 · answered Oct 26 '18 at 01:29

You can certainly do what you are seeking by using a raw text format (using @), but there may be advantages to treating the field as a date, in which case the format is definitely possible. For example, if you sort on this field as text, "Feb 19" will come before "Jan 19," which is probably not what you want.

The raw text solution is simply:

ws.Cells[1, 1].NumberFormat = "@";
ws.Cells[1, 1].Value = "Jan 19";

Presumably, you mean January 2019, in which case any reasonable date format (based on your locale) would result in a date within January, and then a format of mmm yy would render that as "Jan 19:"

ws.Cells[2, 1].Value = "1-Jan-2019";
ws.Cells[2, 1].NumberFormat = "mmm yy";

And just to prove this works, the notion of an Excel date (the number of days after 1/1/1900 or something similar) should even work directly:

ws.Cells[3, 1].Value = "43466";
ws.Cells[3, 1].NumberFormat = "mmm yy";

These last two methods will retain the format you desire and still treat the cells as values that enable sorting and math.

Here are the results of the above three statements:

Interestingly I am guessing the "Jan-19" was probably translated to 1/19/2018, which I am guessing is not what you intended.

C# setting format of text correctly in Excel

3 Answers3