Counting dates by week, months and year formula

06/15/21
Accepted

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 NewcomePaul 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?

    thinkspi.com

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

    thinkspi.com

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    Happy to help! 👍️

    thinkspi.com

Sign In or Register to comment.