Return the number of days in a month from a data set

try this. hth

oh sorry. i looked at it too quick & misunderstood what you were solving for...

so you want to know how many Apr 1st's appear in this list (for example) right? or did you want how many days per each month appear in the list? either way, try this.

tmp.solved.xlsx

But if it is just weekend days that are not included in your data a simpler formula would be

Formula:

Please Login or Register  to view this content.

Originally Posted by 6StringJazzer

pickslides, I believe you are asking how many unique dates are listed for each month.

Your data has some problems. All of your dates for April (except April 3 and 4) are text, not dates, making this process extremely difficult to resolve. I think you may have some localization issue where Excel doesn't recognize 14/4/2014 in Aussie format as a valid date, and so it is interpreted as text. Or you may have actually entered them as text to start with. I'm not sure.

Also, whenever dealing with dates, it is always better to use actual dates. In C7:C10, you have typed in month names as text. If you want to count dates for each month, it is going to be better for these to be dates so you can test the dates in your list against these dates. For example, use 1/4/2014 for April (assuming Aussie date format), then use formatting to display "April".

@6StringJazzer, thanks for explaining the inconsistent raw date inputs - i noticed it too but hoped if OP saw that the month in text format or the pivot was not counting them as USA date formats dates as expected, he could elaborate/make changes needed to fix the formulas i provided (fyi - in fairness I added the 1st col with month as text MMM, to facilitate the countifs per each month)

You are so right about DATES they are TRICKY TRICKY little DEVILS in the programming world, no matter what code language you are writing them in! it was an eye-opener to me as i discovered how complicated they can be when i was first learning .NET programming in VB and JS. Excel is certainly no exception - maybe even more complicated in some ways.