Date Difference For Duplicate Entries

[Pochie]

Hello,

I would like to ask for your assistance in creating formulas under First Day Colum and Last Day Column.

• There are duplicate entries under Programs column
• The First day column shall provide result of the first activity (End date minus Start date)
• The Last day column shall provide results of the last activity (End date minus Start date)
• Activities in between the first day and last day (same activity name) shall have a blank result
• Activity with single entry shall has results for both First day and Last day (End date minus Start date)

Hope you can help me on this, see below sample data.

Programs	Start	End	First Day	Last Day
Activity 1	10/1/20	10/2/20	1
Activity 2	10/1/20	10/2/20	1	1
Activity 3	10/1/20	10/4/20	3
Activity 1	10/1/20	10/3/20
Activity 1	10/1/20	10/4/20		3
Activity 3	10/1/20	10/5/20		4

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this. just make sure where I have used $100 that you choose a row number that will be below your last data row.

20 10 17.xlsm
	A	B	C	D	E
1	Programs	Start	End	First Day	Last Day
2	Activity 1	01-Oct-20	02-Oct-20	1
3	Activity 2	01-Oct-20	02-Oct-20	1	1
4	Activity 3	01-Oct-20	04-Oct-20	3
5	Activity 1	01-Oct-20	03-Oct-20
6	Activity 1	01-Oct-20	04-Oct-20		3
7	Activity 3	01-Oct-20	05-Oct-20		4
8
First Last

Cell Formulas
Range	Formula
D2:D8	D2	=IF(COUNTA(A2:C2)=3,IF(COUNTIF(A$1:A1,A2)=0,C2-B2,""),"")
E2:E8	E2	=IF(COUNTA(A2:C2)=3,IF(COUNTIF(A3:A$100,A2)=0,C2-B2,""),"")

 

[Pochie]

Thank you! It works!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.