brandonmcg
New Member
- Joined
- Jan 14, 2009
- Messages
- 43
- Office Version
- 2016
- Platform
- Windows
I have seen several date models posted to MrExcel like this need but I did not see solution that fits my needs. If you can help that would be great!
The spreadsheet is tracking the progress or the completion of a list of task items. There are fifty or more tasks / rows for this tracking spreadsheet.
These are the headers.
I need all dates to be work days Monday - Friday.
E1 Expected Start Date - Predetermined Date entered by the user.
F1 Actual Start Date - The actual date the project was started. Can be the same as E1 but not necessarily always. Entered by user.
G1 Expected Completion Date - Based on the number in corresponding row and column K. Example Row 3 would correspond with the number in column K3. This would add the number of work days in column K to create this date. Need a formula here.
H1 Days Until Due - This is a countdown. Based on the date in column G would populate this field with the number of workdays days until it is due. Expected completion minus today.
I1 Days Due Category - Based on the number in H1 populate this cell with 7 or more business days = "Due Date a week or more away" Less than 7 days but not the due date. "Due Date is Soon", If the number is zero days "Due Date is Today", Past due 1 to 5 business days "Due Date is Past Due".
The Past Due in Red, 0 days in yellow, 1-3 days orange, 4 plus days green. If color coding is not an option the number is fine.
J1 Follow-up Date-Entered by the user.
K Actual Completion Date-Entered by the user
L1 A number set by number of days we want to allow for the person to complete the task. We enter this number. But it is used to calculate the Expected Completion Due Date.
M1 Delivery Message. "On Time" zero or fewer days, "Delivered Late" 1-3 days, "Significantly Late" Anything greater than 3 days.
The spreadsheet is tracking the progress or the completion of a list of task items. There are fifty or more tasks / rows for this tracking spreadsheet.
These are the headers.
I need all dates to be work days Monday - Friday.
E1 Expected Start Date - Predetermined Date entered by the user.
F1 Actual Start Date - The actual date the project was started. Can be the same as E1 but not necessarily always. Entered by user.
G1 Expected Completion Date - Based on the number in corresponding row and column K. Example Row 3 would correspond with the number in column K3. This would add the number of work days in column K to create this date. Need a formula here.
H1 Days Until Due - This is a countdown. Based on the date in column G would populate this field with the number of workdays days until it is due. Expected completion minus today.
I1 Days Due Category - Based on the number in H1 populate this cell with 7 or more business days = "Due Date a week or more away" Less than 7 days but not the due date. "Due Date is Soon", If the number is zero days "Due Date is Today", Past due 1 to 5 business days "Due Date is Past Due".
The Past Due in Red, 0 days in yellow, 1-3 days orange, 4 plus days green. If color coding is not an option the number is fine.
J1 Follow-up Date-Entered by the user.
K Actual Completion Date-Entered by the user
L1 A number set by number of days we want to allow for the person to complete the task. We enter this number. But it is used to calculate the Expected Completion Due Date.
M1 Delivery Message. "On Time" zero or fewer days, "Delivered Late" 1-3 days, "Significantly Late" Anything greater than 3 days.
