Counting unique number of days in a data set

%3CLINGO-SUB%20id%3D%22lingo-sub-2690958%22%20slang%3D%22en-US%22%3ECounting%20unique%20number%20of%20days%20in%20a%20data%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2690958%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20be%20very%20grateful%20for%20any%20ideas%20or%20advice%20on%20the%20following%20conundrum%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20approx.%20600%20line%20items%20of%20activities%20for%20a%20construction%20gantt%20chart%2C%20with%20Start%20and%20Finish%20dates.%3C%2FP%3E%3CP%3E.%20I%20am%20trying%20to%20count%20only%20the%20total%20number%20of%20%22unique%20dates%22%20(i.e.%20no%20overlaps)%20using%20a%20formula.%3C%2FP%3E%3CP%3E.%20Calculating%20the%20'23'%20in%20the%20below%20example%20is%20easy%20-%20simple%20SUM.%3C%2FP%3E%3CP%3E.%20Trying%20to%20also%20calculate%20the%20'19'%20in%20the%20below%20example%20which%20I%20can't%20solve...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance!%3C%2FP%3E%3CP%3EGeorge%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22666%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2279%22%3E%3CSTRONG%3Eactivity%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2293%22%3E%3CSTRONG%3Estart%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2293%22%3E%3CSTRONG%3Efinish%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2285%22%3E%3CSTRONG%3Eduration%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2272%22%3E%3CSTRONG%3Eoverlap%26nbsp%3B%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22244%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAct-001%3C%2FTD%3E%3CTD%3E1%20May%202021%3C%2FTD%3E%3CTD%3E10%20May%202021%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAct-002%3C%2FTD%3E%3CTD%3E7%20May%202021%3C%2FTD%3E%3CTD%3E15%20May%202021%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E(bec%2F%20these%20already%20exist%20above)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAct-003%3C%2FTD%3E%3CTD%3E20%20May%202021%3C%2FTD%3E%3CTD%3E23%20May%202021%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E23%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E19%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3Eunique%20days%26nbsp%3B%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2690958%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2691080%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20unique%20number%20of%20days%20in%20a%20data%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2691080%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1139104%22%20target%3D%22_blank%22%3E%40George145%3C%2FA%3E%26nbsp%3BPerhaps%20you%20can%20use%20the%20formulae%20demonstrated%20in%20the%20attached%20file.%20It%20calculates%20overlap%20as%20the%20duration%20between%20end%20dates%20for%20subsequent%20activities%2C%20only%20if%20the%20start%20date%20of%20the%20latest%20activity%20lays%20before%20the%20end%20data%20of%20the%20first.%20Otherwise%2C%20it%20returns%20zero.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

 

Many thanks in advance!

George 

 

activitystartfinishdurationoverlap  
      
Act-0011 May 202110 May 202110  
Act-0027 May 202115 May 202194(bec/ these already exist above)
Act-00320 May 202123 May 20214  
   23  
    19unique days 

 

3 Replies

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

@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? 

 

activitystartfinishdurationoverlapunique
      
Act-0011-May-2111-May-2111011
Act-0024-Apr-218-May-2135-439
Act-0039-May-2123-May-2115015
   61-465
Sorry - I just reread the remainder of your note and you had already stated this constraint.