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.
0
Answers
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:)
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.
Happy to help! 👍️
thinkspi.com