[Solved] Count number of dates in current month

maggieyoung76 · Post by **maggieyoung76** » Mon Dec 05, 2016 4:37 pm

Hi all,

I am struggling to find way to Count number of dates in current month. My column has conditional formatting showing all dates that fall in current month. Can you help me count them?

Thank you

Maggie

edit this topic's initial post · Post by **Villeroy** » Mon Dec 05, 2016 4:47 pm

https://wiki.openoffice.org/wiki/Docume ... H_function

maggieyoung76 · Post by **maggieyoung76** » Mon Dec 05, 2016 4:50 pm

Hi, thanks for that.

I don't need number of days in current month or a particular month. I have a column called Date Acquired and need to see the dates in the column that are in the current month. Hope that makes

Maggie

Post by **acknak** » Mon Dec 05, 2016 5:01 pm

If the dates are in A1:A10, something like this should work:

=SUMPRODUCT(MONTH(A1:A10)=MONTH(TODAY()))

Note: if you have empty cells, or non-date cells in that range, the formula will need to be changed or it may give the wrong answer.

maggieyoung76 · Post by **maggieyoung76** » Mon Dec 05, 2016 5:03 pm

Hi there, do you have it so it will work with dates? Thank you so much.

Maggie

Post by **acknak** » Mon Dec 05, 2016 5:38 pm

Yes, that works with dates. MONTH takes a date and returns the month number: MONTH("2016-12-05") returns 12.

If the formula isn't working, your dates may actually be text, in which case it may produce an error.

maggieyoung76 · Post by **maggieyoung76** » Mon Dec 05, 2016 5:53 pm

Hi, my column is set to dates but the formula is returning random numbers for the column range. I've attached the sheet and highlighted where I need the calculation to be. can you please take a peek?

Post by **acknak** » Mon Dec 05, 2016 6:06 pm

The formula won't work with empty cells: it takes an empty cell as zero, which represents a date in December.

The attached sheet has a modified formula that ignores empty cells:
=SUMPRODUCT(M10:M20<>"";MONTH(M10:M20)=MONTH(TODAY()))

Setting a cell format to "Date" does not change the type of data stored in the cell. You can check the data type using View > Value Highlighting: ON: text data will appear in black; numbers or dates in blue.

maggieyoung76 · Post by **maggieyoung76** » Mon Dec 05, 2016 6:08 pm

That's really helpful, thank you very much indeed.

maggie

edit this topic's initial post · Post by **Villeroy** » Mon Dec 05, 2016 11:10 pm

1000 random dates grouped by year and month and counted by a pivot table (aka data pilot).
https://wiki.openoffice.org/wiki/Docume ... /DataPilot

maggieyoung76 · Post by **maggieyoung76** » Tue Dec 06, 2016 11:18 am

Thank you Villeroy!

Maggie

[Solved] Count number of dates in current month

[Solved] Count number of dates in current month

Re: Count number of dates in current month

Re: Count number of dates in current month

Re: Count number of dates in current month

Re: Count number of dates in current month

Re: Count number of dates in current month

Re: Count number of dates in current month

Re: Count number of dates in current month

Re: Count number of dates in current month

Re: [Solved] Count number of dates in current month

Re: [Solved] Count number of dates in current month