Calculating Project Start Date

[Datatellsall2]

Hi There,

I'm analyzing a large amount of time entry data (30,000+ rows). I'm trying to calculate the day a project started and ended for one person...in the example below I'm trying to solve for when the project started and ended for Amy (via a function).

[TABLE="width: 500"]
<tbody>[TR]
[TD]Project #[/TD]
[TD]Name of Person working on project[/TD]
[TD]Date[/TD]
[TD]Day of Entry[/TD]
[TD]Project Start for Amy (day #)[/TD]
[TD]Project End for Amy (day #)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]John[/TD]
[TD]4/1/19[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Amy[/TD]
[TD]4/4/19[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]John[/TD]
[TD]4/5/19[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Amy[/TD]
[TD]4/6/19[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Amy[/TD]
[TD]4/7/19[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks!
M

 

[Scott T]

Try

Book1
	A	B	C	D	E	F
1	Project #	Name of Person working on project	Date	Day of Entry	Project Start for Amy (day #)	Project End for Amy (day #)
2	1	John	4/1/2019	1
3	1	Amy	4/4/2019	4	4
4	1	John	4/5/2019	5
5	1	Amy	4/6/2019	6
6	1	Amy	4/7/2019	7		7
Sheet5

Cell Formulas
Range	Formula
E2	{=IF(B2<>"amy","",IF(MIN(IF($B$2:$B$6="amy",$D$2:$D$6))=D2,D2,""))}
E3	{=IF(B3<>"amy","",IF(MIN(IF($B$2:$B$6="amy",$D$2:$D$6))=D3,D3,""))}
E4	{=IF(B4<>"amy","",IF(MIN(IF($B$2:$B$6="amy",$D$2:$D$6))=D4,D4,""))}
E5	{=IF(B5<>"amy","",IF(MIN(IF($B$2:$B$6="amy",$D$2:$D$6))=D5,D5,""))}
E6	{=IF(B6<>"amy","",IF(MIN(IF($B$2:$B$6="amy",$D$2:$D$6))=D6,D6,""))}
F2	{=IF(B2<>"amy","",IF(MAX(IF($B$2:$B$6="amy",$D$2:$D$6))=D2,D2,""))}
F3	{=IF(B3<>"amy","",IF(MAX(IF($B$2:$B$6="amy",$D$2:$D$6))=D3,D3,""))}
F4	{=IF(B4<>"amy","",IF(MAX(IF($B$2:$B$6="amy",$D$2:$D$6))=D4,D4,""))}
F5	{=IF(B5<>"amy","",IF(MAX(IF($B$2:$B$6="amy",$D$2:$D$6))=D5,D5,""))}
F6	{=IF(B6<>"amy","",IF(MAX(IF($B$2:$B$6="amy",$D$2:$D$6))=D6,D6,""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Scott T]

If all your headers are the same except for the name you can pull the name out of the header so you do not have to hardcode it.

=IF(B2<>MID(E$1,19,SEARCH("(",E$1)-20),"",IF(MIN(IF($B$2:$B$6=MID(E$1,19,SEARCH("(",E$1)-20),$D$2:$D$6))=D2,D2,""))

=IF(B2<>MID(F$1,17,SEARCH("(",F$1)-17),"",IF(MAX(IF($B$2:$B$6=MID(F$1,17,SEARCH("(",F$1)-17),$D$2:$D$6))=D2,D2,""))

Enter with CTRL+SHIFT+ENTER

 

[Datatellsall2]

This is so close! The only other factor I need to consider is that I have multiple projects, so I need the function to look at column A to determine if the same start/end dates are for the same project.

And the names aren't in the header, so I don't think the second message will work...

Thanks!
M

 

[Scott T]

IF you have access to MINIF and MAXIF

E2

=IF(AND(B2="amy",MINIFS($C$2:$C$9,$B$2:$B$9,"amy",$A$2:$A$9,A2)=C2),DAY(MINIFS($C$2:$C$9,$B$2:$B$9,"amy",$A$2:$A$9,A2)),"")

F2

=IF(AND(B2="amy",MAXIFS($C$2:$C$9,$B$2:$B$9,"amy",$A$2:$A$9,A2)=C2),DAY(MAXIFS($C$2:$C$9,$B$2:$B$9,"amy",$A$2:$A$9,A2)),"")

IF you do not have MINIF and MAXIF

E2

=IF(AND(B2="amy",MIN(IF((($B$2:$B$9="amy")*($A$2:$A$9=A2)*($C$2:$C$9)),$C$2:$C$9))=C2),DAY(MIN(IF((($B$2:$B$9="amy")*($A$2:$A$9=A2)*($C$2:$C$9)),$C$2:$C$9))),"")

F2

=IF(AND(B2="amy",MAX(($B$2:$B$9="amy")*($A$2:$A$9=A2)*$C$2:$C$9)=C2),DAY(MAX(($B$2:$B$9="amy")*($A$2:$A$9=A2)*$C$2:$C$9)),"")