# Counting dates by week, months and year formula

Options

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.

Tags:

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

• ✭✭✭✭✭✭
Options

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?

• Options

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.

• ✭✭✭✭✭✭
Options

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()))

• Options

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

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

• edited 10/27/21
Options

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

• ✭✭✭✭✭✭
Options

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)))