How to Check if an Excel Cell of Type Data Date is empty?

Question

The Situation: I'm running into an issue that's driving me insane. I have an Excel file, which I upload to my C# application and use Excel.Interop to run through it.

The Problem: What I am trying to achieve is the following, if the cell I'm iterating through is not null, I treat the data inside it, else I warn the user the cell is empty/null. The issue I'm running into is the following, on of the columns in the excel is Data Type: Date, and for some reason even with an IF statement this large:

if (xlsRange.Cells[i, j] != null || xlsRange.Cells[i, j].Value != null || xlsRange.Cells[i, j].Value2 != null || xlsRange.Cells[i, j].Value != DateTime.MinValue || xlsRange.Cells[i, j].Value.ToString() != DateTime.MinValue || xlsRange.Cells[i, j].Value.ToString() != "" || xlsRange.Cells[i, j].Value.ToString() != " " || xlsRange.Cells[i, j].Value.ToString().Length != 0 || xlsRange.Cells[i, j].Value2 != DateTime.MinValue || xlsRange.Cells[i, j] != DateTime.MinValue || string.IsNullOrEmpty(xlsRange.Cells[i, j].ToString()) == false) {}

The empty cell in the Date data type columns always enter the IF Statement ... All other columns formatted with general data type work like a charm, but Date for some reason which is beyond me, does not. I'm sorry if it's a silly question but I checked a lot of posts here (therefor the huge if statement) and none of them were able to help me achieve what I need. Thanks in advance for any help that could guide me to a solution.

My expected result is that the empty Date Data Type cells do not enter the monstrous IF Statement I have built.

That does seem over the top :-/ Can you split that up to see exactly which condition your empty date cell is triggering? Or temporarily read the various properties into variables first so you can see the values as you step through? — Rup
Isn't this bit always true? xlsRange.Cells[i, j].Value.ToString() != "" || xlsRange.Cells[i, j].Value.ToString() != " ". (Crazy operator overloading aside) There's no result of xlsRange.Cells[i, j].Value that is both equal to " " and "", so that condition is always true. I appreciate your question is really "how do I detect an empty cell of type 'date'", so that doesn't answer your question in itself. — Rup
@Rup, if 'm not mistaken I'm using OR, so if any one of those conditions are met, they will enter the IF Statement, otherwise it shouldn't ... I know I'm way over the top on this one, I just wanted to show that I have tried countless condition and yet the empty date cells in my excel still enter the IF Statement. — Hawk

Kuba Do Kuba Do · Accepted Answer · 2019-08-02T12:56:07

I my program i do check on values in each cell in entire column "I" :

try to rework for Your needs

var col2 = xlWorkSheet.UsedRange.Columns["I:I", Type.Missing];

            foreach (Microsoft.Office.Interop.Excel.Range item in col2.Cells)
            {
                if (Convert.ToString(item.Value) == null)
                {
                    //Change interior color for full row
                    item.EntireRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Cyan);
                }
                else
                {
                    MessageBox.Show("Empty cell");
                }
            }

How to Check if an Excel Cell of Type Data Date is empty?

1 Answers