CODEWITHSUNDEEP
exceldateexcel-formulaformulaformulas
Qralnaq
Qralnaq

Reputation: 73

Excel: Count every date in a column and give back the sum of dates (preferably in *one* formula)

enter image description here

As you will see in my picture, I do have the following problem: I want to find out whether a value in a cell of column A contains a date or not. In order to do so, I have used the following formula in cells B1:B8:

As you will quickly notice, it works for B1:B6 but is faulty when it comes to B6:B7, etc.

So: What is wrong with that formula? Column A is formated as TT.MM.JJJJ (or DD.MM.YYYY) as a whole.

If that formula were to work properly, I could easily count the number of dates in column A using column B. But: Is there a way to do this without having to use column B, that is, checking whether a cell in A contains a date and adding it up in one cell with a respective formula?

Upvotes: 1

Views: 180

Answers (1)

Sven
Sven

Reputation: 2889

You need to check if the cell is empty before checking if it's a date:

=IF(A1!='', IF(ISNUMBER(DAY(A1)),1,0), '')

If you want the sum, you can use 

=COUNTIF(B16:B23,">32874")

32874 is the decimal representation of 1/1/1900, if you need to parse earlier dates, you can just use 1 too.

Upvotes: 1

Related Questions