Assing task date

[MohitMathur]

Hi All,

Please find the attached file, where I am finding date in column 'C' and here condition, if sat and sun comes so ignore the date and jump on Next working day but for Monday it will jump on wed because 09Jul and 10 Jul task already been assigned.

Regards
Mohit


[TABLE="class: cms_table, width: 216"]
<tbody>[TR]
[TD="class: cms_table_xl66, width: 72"]Day[/TD]
[TD="class: cms_table_xl67, width: 72"]Date1[/TD]
[TD="class: cms_table_xl68, width: 72"]Output[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl69"]Thu[/TD]
[TD="class: cms_table_xl65, align: right"]5-Jul-18[/TD]
[TD="class: cms_table_xl70, align: right"]5-Jul-18[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl69"]Thu[/TD]
[TD="class: cms_table_xl65, align: right"]5-Jul-18[/TD]
[TD="class: cms_table_xl70, align: right"]5-Jul-18[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl69"]Thu[/TD]
[TD="class: cms_table_xl65, align: right"]5-Jul-18[/TD]
[TD="class: cms_table_xl70, align: right"]5-Jul-18[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl69"]Fri[/TD]
[TD="class: cms_table_xl65, align: right"]6-Jul-18[/TD]
[TD="class: cms_table_xl70, align: right"]6-Jul-18[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl69"]Fri[/TD]
[TD="class: cms_table_xl65, align: right"]6-Jul-18[/TD]
[TD="class: cms_table_xl70, align: right"]6-Jul-18[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl69"]Fri[/TD]
[TD="class: cms_table_xl65, align: right"]6-Jul-18[/TD]
[TD="class: cms_table_xl70, align: right"]6-Jul-18[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl69"]Sat[/TD]
[TD="class: cms_table_xl65, align: right"]7-Jul-18[/TD]
[TD="class: cms_table_xl70, align: right"]9-Jul-18[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl69"]Sat[/TD]
[TD="class: cms_table_xl65, align: right"]7-Jul-18[/TD]
[TD="class: cms_table_xl70, align: right"]9-Jul-18[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl69"]Sat[/TD]
[TD="class: cms_table_xl65, align: right"]7-Jul-18[/TD]
[TD="class: cms_table_xl70, align: right"]9-Jul-18[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl69"]Sun[/TD]
[TD="class: cms_table_xl65, align: right"]8-Jul-18[/TD]
[TD="class: cms_table_xl70, align: right"]10-Jul-18[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl69"]Sun[/TD]
[TD="class: cms_table_xl65, align: right"]8-Jul-18[/TD]
[TD="class: cms_table_xl70, align: right"]10-Jul-18[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl69"]Sun[/TD]
[TD="class: cms_table_xl65, align: right"]8-Jul-18[/TD]
[TD="class: cms_table_xl70, align: right"]10-Jul-18[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl69"]Mon[/TD]
[TD="class: cms_table_xl65, align: right"]9-Jul-18[/TD]
[TD="class: cms_table_xl71, align: right"]11-Jul-18[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl69"]Mon[/TD]
[TD="class: cms_table_xl65, align: right"]9-Jul-18[/TD]
[TD="class: cms_table_xl71, align: right"]11-Jul-18[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl72"]Mon[/TD]
[TD="class: cms_table_xl73, align: right"]9-Jul-18[/TD]
[TD="class: cms_table_xl74, align: right"]11-Jul-18[/TD]
[/TR]
</tbody>[/TABLE]

 

[mse330]

Hey Mohit,

So what happens to Tuesday task ? It gets assigned in the same day before the task of Monday which has been assigned to Wednesday ? Can you extend the sample data to inlcude the 2nd week as well so we can understand the logic

 

[MohitMathur]

Hey Mohit,

So what happens to Tuesday task? Does it get assigned on the same day before the task of Monday which has been assigned to Wednesday? Can you extend the sample data to include the 2nd week as well so we can understand the logic

"So here task "A" I have to complete on 2nd Jul and " B" on 3rd Jul and so on but when I see Task " F" that comes on Sat and Task "G" on Sunday but that task I have to complete on Mon which is 9th Jul.
so I have to just skip the SAT and SUN but in continues date order."

[TABLE="width: 942"]
<tbody>[TR]
[TD]Day[/TD]
[TD]Task[/TD]
[TD]Date[/TD]
[TD]Result[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mon[/TD]
[TD]A[/TD]
[TD]2-Jul-18[/TD]
[TD]2-Jul-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tue[/TD]
[TD]B[/TD]
[TD]3-Jul-18[/TD]
[TD]3-Jul-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wed[/TD]
[TD]C[/TD]
[TD]4-Jul-18[/TD]
[TD]4-Jul-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thu[/TD]
[TD]D[/TD]
[TD]5-Jul-18[/TD]
[TD]5-Jul-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fri[/TD]
[TD]E[/TD]
[TD]6-Jul-18[/TD]
[TD]6-Jul-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sat[/TD]
[TD]F[/TD]
[TD]7-Jul-18[/TD]
[TD]9-Jul-18[/TD]
[TD]Task F has to complete on 7th but on 7th we have SAT so this task has to move to Next Monday.[/TD]
[/TR]
[TR]
[TD]Sun[/TD]
[TD]G[/TD]
[TD]8-Jul-18[/TD]
[TD]10-Jul-18[/TD]
[TD]Task G have to complete on 8th but on 8th we have SUN so this task has to move to Next Tuesday[/TD]
[/TR]
[TR]
[TD]Mon[/TD]
[TD]H[/TD]
[TD]9-Jul-18[/TD]
[TD]11-Jul-18[/TD]
[TD]for Monday my date should be Wednesday[/TD]
[/TR]
[TR]
[TD]Tue[/TD]
[TD]I[/TD]
[TD]10-Jul-18[/TD]
[TD]12-Jul-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wed[/TD]
[TD]J[/TD]
[TD]11-Jul-18[/TD]
[TD]13-Jul-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thu[/TD]
[TD]K[/TD]
[TD]12-Jul-18[/TD]
[TD]16-Jul-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fri[/TD]
[TD]L[/TD]
[TD]13-Jul-18[/TD]
[TD]17-Jul-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sat[/TD]
[TD]M[/TD]
[TD]14-Jul-18[/TD]
[TD]18-Jul-18[/TD]
[TD]Same here[/TD]
[/TR]
[TR]
[TD]Sun[/TD]
[TD]N[/TD]
[TD]15-Jul-18[/TD]
[TD]19-Jul-18[/TD]
[TD]Same here[/TD]
[/TR]
[TR]
[TD]Mon[/TD]
[TD]O[/TD]
[TD]16-Jul-18[/TD]
[TD]20-Jul-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tue[/TD]
[TD]P[/TD]
[TD]17-Jul-18[/TD]
[TD]23-Jul-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wed[/TD]
[TD]Q[/TD]
[TD]18-Jul-18[/TD]
[TD]24-Jul-18[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Best Regards,
mohit

 

[mse330]

Ok, maybe the easiest way to do it is identify the first date in column D then copy the below formula

Book1
	A	B	C	D	E	F	G
1	Day	Task	Date	Result		Date Day	Result Day
2	Mon	A	02-Jul-18	02-Jul-18		Mon	Mon
3	Tue	B	03-Jul-18	03-Jul-18		Tue	Tue
4	Wed	C	04-Jul-18	04-Jul-18		Wed	Wed
5	Thu	D	05-Jul-18	05-Jul-18		Thu	Thu
6	Fri	E	06-Jul-18	06-Jul-18		Fri	Fri
7	Sat	F	07-Jul-18	09-Jul-18		Sat	Mon
8	Sun	G	08-Jul-18	10-Jul-18		Sun	Tue
9	Mon	H	09-Jul-18	11-Jul-18		Mon	Wed
10	Tue	I	10-Jul-18	12-Jul-18		Tue	Thu
11	Wed	J	11-Jul-18	13-Jul-18		Wed	Fri
12	Thu	K	12-Jul-18	16-Jul-18		Thu	Mon
13	Fri	L	13-Jul-18	17-Jul-18		Fri	Tue
14	Sat	M	14-Jul-18	18-Jul-18		Sat	Wed
15	Sun	N	15-Jul-18	19-Jul-18		Sun	Thu
16	Mon	O	16-Jul-18	20-Jul-18		Mon	Fri
17	Tue	P	17-Jul-18	23-Jul-18		Tue	Mon
18	Wed	Q	18-Jul-18	24-Jul-18		Wed	Tue
Sheet1

Cell Formulas
Range	Formula
D3	=WORKDAY(D2,1)

 

[mse330]

I think my above suggestion is not correct as it is not considering the dates in column C. Again, use the below forumla after manually setting up the first date in column D

Book1
	A	B	C	D	E	F	G
1	Day	Task	Date	Result		Date Day	Result Day
2	Mon	A	02-Jul-18	02-Jul-18		Mon	Mon
3	Tue	B	03-Jul-18	03-Jul-18		Tue	Tue
4	Wed	C	04-Jul-18	04-Jul-18		Wed	Wed
5	Thu	D	05-Jul-18	05-Jul-18		Thu	Thu
6	Fri	E	06-Jul-18	06-Jul-18		Fri	Fri
7	Sat	F	07-Jul-18	09-Jul-18		Sat	Mon
8	Sun	G	08-Jul-18	10-Jul-18		Sun	Tue
9	Mon	H	09-Jul-18	11-Jul-18		Mon	Wed
10	Tue	I	10-Jul-18	12-Jul-18		Tue	Thu
11	Wed	J	11-Jul-18	13-Jul-18		Wed	Fri
12	Thu	K	12-Jul-18	16-Jul-18		Thu	Mon
13	Fri	L	13-Jul-18	17-Jul-18		Fri	Tue
14	Sat	M	14-Jul-18	18-Jul-18		Sat	Wed
15	Sun	N	15-Jul-18	19-Jul-18		Sun	Thu
16	Mon	O	16-Jul-18	20-Jul-18		Mon	Fri
17	Tue	P	17-Jul-18	23-Jul-18		Tue	Mon
18	Wed	Q	18-Jul-18	24-Jul-18		Wed	Tue
Sheet1

Cell Formulas
Range	Formula
D3	=IF(WORKDAY(D2,1)>=C3,WORKDAY(D2,1),IF(OR(WEEKDAY(C3)=1,WEEKDAY(C3)=7),WORKDAY(C3,1),C3))

 

[mse330]

If you want the first cell to be picked up by the formula instead of manual, use the below

=IFERROR(IF(WORKDAY(D1,1)>=C2,WORKDAY(D1,1),IF(OR(WEEKDAY(C2)=1,WEEKDAY(C2)=7),WORKDAY(C2,1),C2)),IF(OR(WEEKDAY(C2)=1,WEEKDAY(C2)=7),WORKDAY(C2,1),C2))