Adding days from a date column

Matt Travis

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?

Paul Newcome

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

Paul Newcome

Are you able to provide a screenshot of the source data?

Matt Travis

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

Paul Newcome

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