Search code examples
exceldateexcel-formulaformulaformulas

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:

  • German Excel formula: =WENN(ISTZAHL(TAG(A1));1;0)
  • English Excel formula: =IF(ISNUMBER(DAY(A1)),1,0)

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?

Solution

  • 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.