Count if function on dates

Praz

Hi,

Can someone help me how to use count if to calculate the no of days that an event happens in a month and create a monthly report based on that? 

Andrée Starå

@Praz

Try something like this. (change the last number, so it represents the corresponding month that you want to count) I'd also recommend adding a so-called helper column with the month numbers already added, and then you could use my second formula instead.)

=
COUNTIFS([Project Completion Date]:[Project Completion Date], 
IFERROR(MONTH(@cell), 0) = 1)

=
COUNTIFS([Project Completion Date]:[Project Completion Date], 
IFERROR(MONTH(@cell), 0) = [Month Numbers]@row)

Did that work?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

Would it span more than 1 month?

Andrée Starå

Hi @Praz

I hope you're well and safe!

Do you only need to know the number of days between two dates?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Praz

No Paul. I have multiple dates in a column all over the year and I need to count and sort it based on months.

Praz

Hi Andre,

Thanks for your comments. Actually I need to count how many days that occurrence fall in a month. i have a list of dates in a column and I need to count and sort based on the months.

Gareth Jeanes

Hi @Praz ,

Could you send a photo of an example of the data to assist in understanding your problem?

Cheers

Gareth

Praz

Andrée Starå

@Praz

Try something like this. (change the last number, so it represents the corresponding month that you want to count) I'd also recommend adding a so-called helper column with the month numbers already added, and then you could use my second formula instead.)

=
COUNTIFS([Project Completion Date]:[Project Completion Date], 
IFERROR(MONTH(@cell), 0) = 1)

=
COUNTIFS([Project Completion Date]:[Project Completion Date], 
IFERROR(MONTH(@cell), 0) = [Month Numbers]@row)

Did that work?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Praz

Got it Andree... It really worked. Thanks a ton

Andrée Starå

@Praz

Excellent!

Happy to help!

Gareth Jeanes

HI @Praz,

Another option to @Andrée Starå is:

Have a month helper column:

=MONTH([Project Completion Date]@row)

In sheet Summary:

=COUNTIF([Month Helper]:[Month Helper], 1) - Do this for Jnauary and then so on changing the number.ni

Then use a report based on summary.

Cheers,

Gareth