How can I prevent Excel from converting values to date format?

Question

Recently (nobody noticed it happening before, if it did) some "ID"-type values are being converted by Excel to dates. There is some logic to which values are being converted, as you can see here, where the user added a column to show what the underlying values really are, and how they should be represented (raw, no conversion):

So because there's a "-" after "01" Excel is thinking that "01-" should be January, an assuming the final two characters represent the year.

In Item codes that contain no dash, they are left alone. How can I prevent Excel from being "helpful" in this way and converting these values to dates?

UPDATE

In response to Scott Craner's comment, this is the code I have to write out that value:

using (var memberItemCodeCell = priceComplianceWorksheet.Cells[rowToPopulate, DETAIL_MEMBERITEMCODE_COL])
{
    memberItemCodeCell.Style.Font.Size = DATA_FONT_SIZE;
    memberItemCodeCell.Value = _memberItemCode;
    memberItemCodeCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}

...and when I try to figure out how to format the value as Text or General by typing "for" after the cells name, I see these options:

So what do I need to use - FormatedText (sic) or ConditionalFormatting, and how specifically do I set those up to format the column as Text or General, and which if any of these two are preferred?

Yow will want to add the language tag to your OP. So those that know the language are alerted. — Scott Craner, Sep 12 '16 at 15:17
I'm not using VBA; I'm creating the spreadsheets in C# using EPPlus (in Visual Studio); see the Update for the code. — B. Clay Shannon-B. Crow Raven, Sep 12 '16 at 15:22
The `General` format will not stay if you enter a value that could be misconstrued as being a date. To me it seems that only the `text` format is a reliable option. — Carsten Massmann, Sep 12 '16 at 15:23
I think what you are looking for is `memberitemCodeCell.numberformat="@"` assuming that `memberitemCodeCell` references the target range in your Excel sheet. — Carsten Massmann, Sep 12 '16 at 15:29

score 0 · Answer 1 · edited Sep 12 '16 at 16:14

0

I generally when preparing the table to put data, I define data type so Excel don't try to find the kind of data.

In this case I would use Text data type. Example:

edited Sep 12 '16 at 16:14

Doug Glancy

27,214
6
67
115

answered Sep 12 '16 at 16:04

Urbano Freitas

9
2

score 0 · Answer 2 · answered Sep 12 '16 at 16:30

0

I think you want to change the number format to text.

The text format is specified with the NumberFormat property using the "at" character.

memberItemCodeCell.Style.Font.Size = DATA_FONT_SIZE;
memberItemCodeCell.NumberFormat = "@";
memberItemCodeCell.Value = _memberItemCode;
memberItemCodeCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

Also, for what it's worth, take a look at articles / questions regarding the difference between Value and Value2. It's good to understand the difference and use the proper one. In your case, I doubt it matters.

answered Sep 12 '16 at 16:30

Hambone

15,600
8
46
69

In EPPlus it's slightly different: memberItemCodeCell.Style.Numberformat.Format = "@"; And there is no Value2 in EPPlus. – B. Clay Shannon-B. Crow Raven Sep 12 '16 at 16:33
This may prevent the converting-to-date problem, but I still get a green triangle complaining "Number stored as text" – B. Clay Shannon-B. Crow Raven Sep 12 '16 at 16:40
1

Okay, I admit I didn't know what EPPlus was... now I do. I saw the code and assumed it was COM. As far as the triangle. I think that's just a warning from Excel. I could be wrong, but I think this is an Excel-level setting, meaning you can resolve it on your machine, but there is no guarantee that it won't throw the same warning on another. That said, it's really not a problem. It's just Excel warning you it might be a mistake. – Hambone Sep 12 '16 at 16:50
Yes, I know the warnings can be turned off on the individual user's machines, but they don't want to have to see them and do that. So I need a programmatic way of preventing the warning (mean green triangle, or delta) from displaying regardless of the user's Excel settings. – B. Clay Shannon-B. Crow Raven Sep 12 '16 at 16:52
I didn't know that was possible... I'm curious to see what responses you get. – Hambone Sep 12 '16 at 17:02

B. Clay Shannon-B. Crow Raven · Accepted Answer · 2016-09-12T20:39:48.163

What ended up working for me was using this technique to assign the proper data type to the value in the cell (with the call to ConvertValueToAppropriateTypeAndAssign()) and then formatting as necessary after the fact:

public static readonly string NUMBER_FORMAT_CURRENCY = "$#,##0.00;($#,##0.00)";
public static readonly string NUMBER_FORMAT_THOUSANDS = "#,##0";
public static readonly string PERCENTAGE_FORMAT = "0.00%;[Red]-0.00%";
public static readonly string NUMBER_FORMAT_TEXT = "@";
public static readonly string NUMBER_FORMAT_DOUBLE = "0.00"; 

. . .

using (var percentageCell = priceComplianceWorksheet.Cells[rowToPopulate, SUMMARY_PERCENTAGE_COL])
{
    ConvertValueToAppropriateTypeAndAssign(percentageCell, totalPercentage);
    percentageCell.Style.Numberformat.Format = PERCENTAGE_FORMAT;
}

. . .

// Adapted from https://stackoverflow.com/questions/26483496/is-it-possible-to-ignore-excel-warnings-when-generating-spreadsheets-using-epplu
public static void ConvertValueToAppropriateTypeAndAssign(this ExcelRangeBase range, object value)
{
    string strVal = value.ToString();
    if (!String.IsNullOrEmpty(strVal))
    {
        decimal decVal;
        double dVal;
        int iVal;

        if (decimal.TryParse(strVal, out decVal))
            range.Value = decVal;
        if (double.TryParse(strVal, out dVal))
            range.Value = dVal;
        else if (Int32.TryParse(strVal, out iVal))
            range.Value = iVal;
        else
            range.Value = strVal;
    }
    else
        range.Value = null;
}

How can I prevent Excel from converting values to date format?

UPDATE

3 Answers3