Counting dates by week, months and year formula

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.



Best Answer

Answers

  • Paul Newcome
    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?

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

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

  • Laura Byrne
    Laura Byrne ✭✭
    edited 10/27/21

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!