Forum Discussion
Iterating and comparing rows
Hi danextian - thanks for your help.
The hours = 8 hours x number of working days.
The projected completion date is the end of the previously ranked project + working days. For example ID 5, top ranked project finishes on 15/08/22. ID 5 second ranked project then has a completion date of 15/8/22 + 5 working days being 22/8/22 (due to weekend days).
I am confused how the rank is calculated. For ID5, it appears that they're ranked by priority. For 7, they are ranked by Due Date.
Hi danextian,
The logic is to rank by due date, then check if a higher prioirity task is not ontime and if so promote it up the ranks.
For ID 7 they are ranked by due date, and the higher priority task is on time, so no changes are needed.
I've played out the example process for ID 5 below.
STEP 1 - Sort by due date
AssignedToID Due Date Priority (999 = highest) WorkingDaysRemaining 5 1/08/2022 200 5 5 15/08/2022 900 10 5 20/08/2022 500 5 6 1/08/2022 100 5 6 13/08/2022 500 5 6 15/08/2022 500 5 7 5/08/2022 100 7 7 10/08/2022 100 4 7 30/08/2022 900 2 STEP 2 - Iterate and check whether on time, promote if needed.
EG.
Row 2, ONTIME = N, so check whether Row 2 is a higher priority than Row 1.
900 > 200 so move Row 2 up rank.
AssignedToID Due Date Priority (999 = highest) WorkingDaysRemaining ProjectedEndDate Ontime 5 1/08/2022 200 5 8/08/2022 N 5 15/08/2022 900 10 22/08/2022 N 5 20/08/2022 500 5 29/08/2022 N 6 1/08/2022 100 5 8/08/2022 N 6 13/08/2022 500 5 15/08/2022 N 6 15/08/2022 500 5 22/08/2022 Y 7 5/08/2022 100 7 10/08/2022 N 7 10/08/2022 100 4 16/08/2022 N 7 30/08/2022 900 2 18/08/2022 Y Then EG
Row 3, ONTIME = N, so check whether Row 3 is higher priority than Row 2.
Priority 500> 200, so move Row 3 up rank
ONTIME still = No
Check whether higher priority than Row 1
Priority 500 < 900 so do not move higher.
AssignedToID Due Date Priority (999 = highest) WorkingDaysRemaining ProjectedEndDate Ontime 5 15/08/2022 900 10 15/08/2022 Y 5 1/08/2022 200 5 22/08/2022 N 5 20/08/2022 500 5 29/08/2022 N 6 1/08/2022 100 5 8/08/2022 N 6 13/08/2022 500 5 15/08/2022 N 6 15/08/2022 500 5 22/08/2022 Y 7 5/08/2022 100 7 10/08/2022 N 7 10/08/2022 100 4 16/08/2022 N 7 30/08/2022 900 2 18/08/2022 Y Finished iteration of ID5
AssignedToID Due Date Priority (999 = highest) WorkingDaysRemaining Rank ProjectedEndDate Ontime 5 15/08/2022 900 10 1 15/08/2022 Y 5 20/08/2022 500 5 2 22/08/2022 N 5 1/08/2022 200 5 3 29/08/2022 N 6 1/08/2022 100 5 8/08/2022 N 6 13/08/2022 500 5 15/08/2022 N 6 15/08/2022 500 5 22/08/2022 Y 7 5/08/2022 100 7 10/08/2022 N 7 10/08/2022 100 4 16/08/2022 N 7 30/08/2022 900 2 18/08/2022 Y In step 2, how would one know whether a project is on-time or not? Also in row 2, 15/8 + 10 workdays shouldn't that be 29/8?
If ProjectedEndDate is on or before DueDate than the project is ontime
Take "TODAY" as 1 August. For example in Step 2 (screenshot below)
- Row one is 5 days and so ProjectedEndDate is 8 August
- Row two is 10 days and so ProjectedEndDate is 10 working days AFTER the end date of Row 1 (so 8 August + 10wd = 22 August)