New Contributor

Counting unique number of days in a data set

I would be very grateful for any ideas or advice on the following conundrum:

I have approx. 600 line items of activities for a construction gantt chart, with Start and Finish dates.

. I am trying to count only the total number of "unique dates" (i.e. no overlaps) using a formula.

. Calculating the '23' in the below example is easy - simple SUM.

. Trying to also calculate the '19' in the below example which I can't solve...

George

 activity start finish duration overlap Act-001 1 May 2021 10 May 2021 10 Act-002 7 May 2021 15 May 2021 9 4 (bec/ these already exist above) Act-003 20 May 2021 23 May 2021 4 23 19 unique days

3 Replies

Re: Counting unique number of days in a data set

@George145 Perhaps you can use the formulae demonstrated in the attached file. It calculates overlap as the duration between end dates for subsequent activities, only if the start date of the latest activity lays before the end data of the first. Otherwise, it returns zero.

Re: Counting unique number of days in a data set

@Riny_van_Eekelen Thank you, but I don't think it will work if the start date on activities in the subsequent rows is earlier than the start date on activities in the preceding rows. Thoughts?

 activity start finish duration overlap unique Act-001 1-May-21 11-May-21 11 0 11 Act-002 4-Apr-21 8-May-21 35 -4 39 Act-003 9-May-21 23-May-21 15 0 15 61 -4 65