How to get Project Start date and End date in Excel 2016

[Ankitsharmaece]

Please let me know how can I get start date and end date automatically based on on table.
As per table below, I want to calculate start date and end date automatically based on target achieved.

				21-May		22-May		23-May		24-May		25-May		26-May
Work start Date	work end Date	Target qty	Completed Qty	A	B	A	B	A	B	A	B	A	B	A	B
22-May	26-May-23	20	20				10			5		2	2	1

1.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1					21-May		22-May		23-May		24-May		25-May		26-May
2	Work start Date	work end Date	Target qty	Completed Qty	A	B	A	B	A	B	A	B	A	B	A	B
3	22-May	26-May-23	20	20				10			5		2	2	1
Sheet2

Cell Formulas
Range	Formula
H1,P1,N1,L1,J1	H1	=F1+1
E3	E3	=SUM(F3:Q3)

 

[awoohaw]

try this:

Mr excel questions 40.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1					Sun 23/5/21		Mon 23/5/22		Tue 23/5/23		Wed 23/5/24		Thu 23/5/25		Fri 23/5/26
2	Work start Date	work end Date	Target qty	Completed Qty	A	B	A	B	A	B	A	B	A	B	A	B
3	Mon 23/5/22	Fri 23/5/26						10			5		2	2	1
Ankitsharmaece

Cell Formulas
Range	Formula
G1,O1,M1,K1,I1	G1	=E1+1
A3	A3	=INDEX(IF(E1:P1="",D1:O1,E1:P1), 1/AGGREGATE(14,6,1/((ISNUMBER(E3:P3))*(COLUMN(E3:P3)-4)),1))
B3	B3	=INDEX(IF(E1:P1="",D1:O1,E1:P1), AGGREGATE(14,6,((ISNUMBER(E3:P3))*(COLUMN(E3:P3)-4)),1))

It does require unmerged cells in row 1.

 

[Ankitsharmaece]

I tried and copied same code in my excel. but it is giving me #VALUE! Error. I am using MS excel 2016
- I did not see any relation with target qty. you did not put any value in target quantity C3 cell. then how can we say that project completed in B3 (Work end date).
- Can you elaborate the formula little bit. I did not understand the logic.

currently not working for me. please help.

 

[Ankitsharmaece]

OQC Unloader MasterFile.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1					Sunday, May 21, 2023		Monday, May 22, 2023		Tuesday, May 23, 2023		Wednesday, May 24, 2023		Thursday, May 25, 2023		Friday, May 26, 2023
2	Work start Date	work end Date	Target qty	Completed Qty	A	B	A	B	A	B	A	B	A	B	A	B
3	#VALUE!	#VALUE!	20	20				10			5		2	2	1
Sheet10

Cell Formulas
Range	Formula
G1,O1,M1,K1,I1	G1	=E1+1
A3	A3	=INDEX(IF(E1:P1="",D1:O1,E1:P1),1/AGGREGATE(14,6,1/((ISNUMBER(E3:P3))*(COLUMN(E3:P3)-4)),1))
B3	B3	=INDEX(IF(E1:P1="",D1:O1,E1:P1),AGGREGATE(14,6,((ISNUMBER(E3:P3))*(COLUMN(E3:P3)-4)),1))
D3	D3	=SUM(E3:P3)