Adding days from a date column
Hi,
I'm looking to do a monthly, weekly and daily average figure for calls logged. Being as calls won't be logged every day of the year and there are personal holidays within the team to consider, I can't do this with a simple calculation.
I currently have an automatically updated closure date column so in theory if someone closes a call on a date, that proves they are working that day. I would like to use that as my guide for number of days working.
Anyone got any ideas on how I can count the days by month, week and day to use for creating the averages?
Best Answer
-
Ok. To get started with counting how many days per month and per year, you would use something along the lines of...
For May of 2021:
=COUNTIFS([Closure Date]:[Closure Date], AND(IFERROR(MONTH(@cell), 0) = 5, IFERROR(YEAR(@cell), 0) = 2021), [Assigned To]:[Assigned To], "John Doe")
For all of 2021:
=COUNTIFS([Closure Date]:[Closure Date], IFERROR(YEAR(@cell), 0) = 2021, [Assigned To]:[Assigned To], "John Doe")
Answers
-
Are you able to provide a screenshot of the source data?
-
Hi Paul,
Screenshot added of what should be the relevant data. The only other relevant column would be that we have an assigned to column for who the call is being taken on by. Also in case it has any relevance the closure date is automatically populated with today's date when the status is set to complete and similar to that the Awaiting Response Date is populated automatically when the status is set to Awaiting Response (This is used for something else).
-
Ok. To get started with counting how many days per month and per year, you would use something along the lines of...
For May of 2021:
=COUNTIFS([Closure Date]:[Closure Date], AND(IFERROR(MONTH(@cell), 0) = 5, IFERROR(YEAR(@cell), 0) = 2021), [Assigned To]:[Assigned To], "John Doe")
For all of 2021:
=COUNTIFS([Closure Date]:[Closure Date], IFERROR(YEAR(@cell), 0) = 2021, [Assigned To]:[Assigned To], "John Doe")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!