counting date values in Excel

Hello,

I'm looking for a formula like COUNTIF (or similar) in Excel 2010 to count the number of date values in a particular row. See screen shot attached to give you an idea of what I am looking to do. I am guessing this requires testing to see if the content of a particular cell is date in order to count it as date.

Thoughts!

Thank you.
SampleScreen.pdf
htamraz1Director of TechnologyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Richard DanekeTrainerCommented:
Using an embedded if statement, you can count the dates.
Sample file attached.

It is not sophisticated, but a date is a number.  So a test on the date cell can count as 1.
Hence:
=IF(ISNUMBER(B8),1,0)+IF(ISNUMBER(E8),1,0)+IF(ISNUMBER(H8),1,0)

Open in new window

Counts the dates in the three cells when otherwise the cell is blank or text.

The challenge on a CountIf is creating the noncontiguous range and in defining the criteria.  Hopefully, you do not have too many date columns to count.
EECountDates.xlsx
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may use an UDF (User Defined Function) to achieve this easily.
If you are open to this kind of solution, please follow these steps....

1) On your worksheet, press Alt+F11 --> VBA Editor will be opened.
2) On VBA Editor --> Click on Insert --> Module --> A code window will be opened.
3) Paste the code given below in the code window.
4) Close the VBA Editor
5) Save your workbook as Macro-Enabled Workbook.

How to use the UDF?
The UDF in this case is called CountMyDates which takes only one argument i.e. a range.
So in any cell type =CountMyDates(<Select or type the Range to be tested for the dates>) i.e. if you want to count the dates in the range A2:G2, use this UDF like this.....

=CountMyDates(A2:G2)

You can drag the function down or right as you usually do with the excel inbuilt functions.

Function CountMyDates(rng As Range) As Long
    Dim cell As Range
    Dim cnt As Long
    For Each cell In rng
        If IsDate(cell) Then cnt = cnt + 1
    Next cell
    CountMyDates = cnt
End Function

Open in new window


For more detail, see the attached workbook. I have used the file attached by Richard.
EECountDates.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
htamraz1Director of TechnologyAuthor Commented:
Excellent! This did it.
Thank you
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad I could help. :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.