Kellie220
New Member
- Joined
- Jan 23, 2024
- Messages
- 31
- Office Version
- 365
- Platform
- Windows
HI All. I have a simple spreadsheet that I have to calculate a few different columns based on a Due Date. We give out assignments to vendors and sometimes we receive an assignment that has a due date that we cannot meet. We need to make the vendor aware of these due dates even though they might not be met and we later calculate a discount. But if we meet the deadline, more incentives.
Column A = Assignment Name (constant variable)
Column B = Due date of Assignment (Constant variable and what we calculate off of)
Column C = Date rec'd from our planning team, when the assignment can be performed.
Column D = Date we submit to vendor for bidding
Column E = Date vendor agrees to assignment
Column F = when the vendor states they can complete the job by
Estimated time to complete an assignment is 60 days from Date Rec'd or 45 days from Check In Date.
Column B Has my Due Date: I need 3 formulas.
First Formula would let me know if this assignment was rec'd on time
(This would be based off Date rec'd and Due date. If the Due date is less than 60 days from Date rec'd, this would be marked LATE REC'D, If more that 60 days, marked On Time)
Second Formula would let me know if this assignment can be completed on time
(This would be based off Check In and Due date. If the Due date is less than 45 days from Check In date, this would be marked LATE REC'D, if more than 45 days, marked On Time)
Third Formula would let me know if this assignment is scheduled to be completed on time.
(This would be based off Est Closed Date and the Due Date. If the Est Closed Date is less than the Due date then marked "Projected on Time". If it does not meet the Due date, "Project Late"
Lastly, I Index Match to get Column F - Estimated Closed date from a vendor report. Sometimes they give us one date, then the next report the date changes. I would like to see if there is a VBA that would highlight if column F has a different value when I index match the information based on Column A which is a constant value? I am not sure this can be done. I just want to see if the vendors are changing when they can complete the assignment.
Everything we do is tracked by Due Date (Column B) and if one report states it will be on time, then the next report later, we need to know why it was changed. I just would like to highlight those for research,. I Index match weekly.
Although the above looks simple, I am dealing with about 95 different columns explaining the scope of the assignment and about 7000 rows (or more) of assignments. So any help to make this easier to track, would be greatly appreciated.
Column A = Assignment Name (constant variable)
Column B = Due date of Assignment (Constant variable and what we calculate off of)
Column C = Date rec'd from our planning team, when the assignment can be performed.
Column D = Date we submit to vendor for bidding
Column E = Date vendor agrees to assignment
Column F = when the vendor states they can complete the job by
Estimated time to complete an assignment is 60 days from Date Rec'd or 45 days from Check In Date.
Column B Has my Due Date: I need 3 formulas.
First Formula would let me know if this assignment was rec'd on time
(This would be based off Date rec'd and Due date. If the Due date is less than 60 days from Date rec'd, this would be marked LATE REC'D, If more that 60 days, marked On Time)
Second Formula would let me know if this assignment can be completed on time
(This would be based off Check In and Due date. If the Due date is less than 45 days from Check In date, this would be marked LATE REC'D, if more than 45 days, marked On Time)
Third Formula would let me know if this assignment is scheduled to be completed on time.
(This would be based off Est Closed Date and the Due Date. If the Est Closed Date is less than the Due date then marked "Projected on Time". If it does not meet the Due date, "Project Late"
| Column A | Column B | Column C | Column D | Column E | Column F |
| Assignment | Due Date | Date Rec'd | Date Submitted | Check In date | Est Closed Date |
| CR124999 | 2/20/2024 | 1/1/2024 | 1/5/2024 | 1/15/2024 | 5/1/2024 |
| MA052366 | 6/1/2024 | 12/23/2023 | 1/5/2024 | 1/15/2024 | 5/1/2024 |
| MB102095 | 4/1/2024 | 2/27/2023 | 2/29/2024 | 3/10/2024 | 5/1/2024 |
| SB051593 | 8/1/2024 | 2/27/2023 | 2/29/2024 | 3/10/2024 | 10/1/2024 |
Lastly, I Index Match to get Column F - Estimated Closed date from a vendor report. Sometimes they give us one date, then the next report the date changes. I would like to see if there is a VBA that would highlight if column F has a different value when I index match the information based on Column A which is a constant value? I am not sure this can be done. I just want to see if the vendors are changing when they can complete the assignment.
Everything we do is tracked by Due Date (Column B) and if one report states it will be on time, then the next report later, we need to know why it was changed. I just would like to highlight those for research,. I Index match weekly.
Although the above looks simple, I am dealing with about 95 different columns explaining the scope of the assignment and about 7000 rows (or more) of assignments. So any help to make this easier to track, would be greatly appreciated.
