Counting dates by week, months and year formula

Laura Byrne

Hi,

I have a SmartSheet which logs enquiries. I need to create KPI report to measure the number on enquiries that are logged that day, this week, this month, last month, and year. Also a monthly running today for example Jan = 40, Feb = 50 and so on.

Below is a screenshot of my Smartsheet and the column used for the date is 'Date Logged'. Below is a screenshot of the KPI's i need to monitor.

Please could someone provide me with a formula for each and also one for the monthly totals. Thanks so much.

Paul Newcome

Happy to help! 👍️

Paul Newcome

When you say "This Week" do you mean starting on this past Sunday until today or do you mean starting 7 days prior to today? Would "Last Week" be the previous calendar week or would you want anything that was logged between 8 and 14 days ago? Same for months and years?

Laura Byrne

Hi Paul,

This week starting this past Sunday and last week being previous calendar week. It is for monitoring how many enquiries we have from customers each calendar week, month and so on.

Thanks for your help:)

Paul Newcome

Ok. Give these a try...

Today:

=COUNTIFS({Source Sheet Date Column}, @cell = TODAY())

Current week:

=COUNTIFS({Source Sheet Date Column}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))

Previous week:

=COUNTIFS({Source Sheet Date Column}, IFERROR(WEEKNUMBER(@cell), 0) = IF(WEEKNUMBER(TODAY()) = 1, WEEKNUMBER(DATE(YEAR(TODAY()), 1, 1) - 1), WEEKNUMBER(TODAY()) - 1))

Current Month:

=COUNTIFS({Source Sheet Date Column}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

Previous Month:

=COUNTIFS({Source Sheet Date Column}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()))))

Current Year:

=COUNTIFS({Source Sheet Date Column}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

Laura Byrne

That is great!! It's worked perfectly - thank you so much!! Really really appreciated.

Paul Newcome

Happy to help! 👍️

Laura Byrne

Hi @Paul Newcome

I was wondering if you could help me again please, similar to the above but I need to count the number of enquiries for this year (based on our financial year from April - March). I am struggling to come up with the formula.

Thanks,

Laura

Paul Newcome

There are a few different ways to do that, but the most straightforward would be something like this...

=COUNTIFS({Date Column}, AND(@cell >= DATE(2021, 04, 01), @cell < DATE(2022, 04, 01)))

A few notes about this...

Notice the operators. The first is "greater than or equal to", but the second is only "less than". Doing this means you don't have to worry about how many days are in the previous month. You can just enter the same date for both arguments (the start of the fiscal year) and be done with it.

Using this more simplified method, you will need to go in and change the year portion of each date function every year. Some other options would be to build in some IF statements, or use a reference directly to another cell that houses the year(s).