Average formula with dates

Lisanne

Hi all,

In our sheets we track the time we spend on a specific task on a certain day.

So in our sheets we have for example:

Due Date: 1-07-2019; Duration: 10m

We can have multiple tasks for the same day.

I am trying to come up with a formula in which I can calculate the average spend over the past 60 days on a client. However, because there are lines with a duration from the same day I get stuck with the average formula (as the average formula does not look on a daily basis).

I have tried different things but they cannot provide the right outcome (which I understand but I don't know what will):

=SUMIFS((Duration:Duration; [Due Date]:[Due Date]; <=TODAY(); [Due Date]:[Due Date]; >=TODAY(-60)) * 8) / NETDAYS(([Due Date]:[Due Date]; <=TODAY()); ([Due Date]:[Due Date] >=TODAY(-60)))

=AVG(COLLECT(Duration:Duration; [Due Date]:[Due Date]; <=TODAY(-5); [Due Date]:[Due Date]; >=TODAY(-60))) * 8 * 5

=AVG(SUMIFS(Duration:Duration; [Due Date]:[Due Date]; <=TODAY(-5); [Due Date]:[Due Date]; >=TODAY(-60)) * 8)

Can anybody help?

Alejandra

Hi Lisanne,

If you're goal is to find the average task duration over a 60 day period without including rows that contain duplicate date values, then you'll want to create a helper (checkbox) column to identify rows with duplicate values.

The formula below will leave only one of the duplicate values unchecked (this value will be accounted for in the total average):

=IF(LEN([Due Date]@row) = 0, "", IF(COUNTIFS([Due Date]1:[Due Date]4, [Due Date]@row) = 1, 0, 1))

This is what your average formula could look like:

=AVG(COLLECT(Duration:Duration, [Due Date]:[Due Date], <=TODAY(-5), [Due Date]:[Due Date], >=TODAY(-60), Duplicate:Duplicate, 0))