Assing task date

M

MohitMathur

New Member
Joined
May 26, 2014
Messages
12
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]
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
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
 
Upvote 0
mse330 said:
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
Click to expand...


"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
 
Upvote 0
Ok, maybe the easiest way to do it is identify the first date in column D then copy the below formula


Book1
ABCDEFG
1DayTaskDateResultDate DayResult Day
2MonA02-Jul-1802-Jul-18MonMon
3TueB03-Jul-1803-Jul-18TueTue
4WedC04-Jul-1804-Jul-18WedWed
5ThuD05-Jul-1805-Jul-18ThuThu
6FriE06-Jul-1806-Jul-18FriFri
7SatF07-Jul-1809-Jul-18SatMon
8SunG08-Jul-1810-Jul-18SunTue
9MonH09-Jul-1811-Jul-18MonWed
10TueI10-Jul-1812-Jul-18TueThu
11WedJ11-Jul-1813-Jul-18WedFri
12ThuK12-Jul-1816-Jul-18ThuMon
13FriL13-Jul-1817-Jul-18FriTue
14SatM14-Jul-1818-Jul-18SatWed
15SunN15-Jul-1819-Jul-18SunThu
16MonO16-Jul-1820-Jul-18MonFri
17TueP17-Jul-1823-Jul-18TueMon
18WedQ18-Jul-1824-Jul-18WedTue
Sheet1
Cell Formulas
RangeFormula
D3=WORKDAY(D2,1)
 
Upvote 0
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
ABCDEFG
1DayTaskDateResultDate DayResult Day
2MonA02-Jul-1802-Jul-18MonMon
3TueB03-Jul-1803-Jul-18TueTue
4WedC04-Jul-1804-Jul-18WedWed
5ThuD05-Jul-1805-Jul-18ThuThu
6FriE06-Jul-1806-Jul-18FriFri
7SatF07-Jul-1809-Jul-18SatMon
8SunG08-Jul-1810-Jul-18SunTue
9MonH09-Jul-1811-Jul-18MonWed
10TueI10-Jul-1812-Jul-18TueThu
11WedJ11-Jul-1813-Jul-18WedFri
12ThuK12-Jul-1816-Jul-18ThuMon
13FriL13-Jul-1817-Jul-18FriTue
14SatM14-Jul-1818-Jul-18SatWed
15SunN15-Jul-1819-Jul-18SunThu
16MonO16-Jul-1820-Jul-18MonFri
17TueP17-Jul-1823-Jul-18TueMon
18WedQ18-Jul-1824-Jul-18WedTue
Sheet1
Cell Formulas
RangeFormula
D3=IF(WORKDAY(D2,1)>=C3,WORKDAY(D2,1),IF(OR(WEEKDAY(C3)=1,WEEKDAY(C3)=7),WORKDAY(C3,1),C3))
 
Upvote 0
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))
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Problem with x-axis labels in excel chart
Replies
1
Views
375
sharshra
S
C
  • Question Question
SUMIFS EOM
Replies
2
Views
266
ChetanPuri
C
H
  • Solved
Excel Formula - Index Match or Something Else ??
Replies
4
Views
181
hrayani
H
S
  • Solved
Calculate Backdated dates
Replies
5
Views
268
Joe4
Joe4
R
  • Question Question
Identifying the difference between two data sets
Replies
1
Views
165
igold
igold

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top