Formula for calculating different dates/ VBA

Kellie220

New Member
Joined
Jan 23, 2024
Messages
30
Office Version
  1. 365
Platform
  1. 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"

Column AColumn BColumn CColumn DColumn EColumn F
AssignmentDue DateDate Rec'dDate SubmittedCheck In dateEst Closed Date
CR1249992/20/20241/1/20241/5/2024
1/15/2024​
5/1/2024​
MA0523666/1/202412/23/20231/5/2024
1/15/2024​
5/1/2024​
MB1020954/1/20242/27/20232/29/2024
3/10/2024​
5/1/2024​
SB0515938/1/20242/27/20232/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. :)
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
do these work for you?

Book1
ABCDEFGHI
1AssignmentDue DateDate Rec'dDate SubmittedCheck In dateEst Closed DateRec'd On TimeCan meet timelineCompleted on Time
22024-02-062024-04-072024-04-04On TimeOn TimeProjected On Time
32024-02-082024-04-072024-04-07LATE REC'DOn TimeProject Late
42024-02-092024-04-062024-04-08LATE REC'DOn TimeProject Late
52024-02-172024-04-052024-04-04LATE REC'DOn TimeProjected On Time
62024-02-282024-04-072024-04-04LATE REC'DLATE REC'DProjected On Time
72024-02-292024-04-082024-04-05LATE REC'DLATE REC'DProjected On Time
82024-03-012024-04-082024-04-05LATE REC'DLATE REC'DProjected On Time
92024-03-102024-04-072024-04-09LATE REC'DLATE REC'DProject Late
102024-04-022024-04-052024-04-05LATE REC'DLATE REC'DProject Late
112024-04-022024-04-072024-04-06LATE REC'DLATE REC'DProjected On Time
12
13AssignmentDue DateDate Rec'dDate SubmittedCheck In dateEst Closed Date
14CR1249992024-02-202024-01-012024-01-052024-01-152024-05-01LATE REC'DLATE REC'DProject Late
15MA0523662024-06-012023-12-232024-01-052024-01-152024-05-01LATE REC'DLATE REC'DProject Late
16MB1020952024-04-012023-02-272024-02-292024-03-102024-05-01LATE REC'DLATE REC'DProject Late
17SB0515932024-08-012023-02-272024-02-292024-03-102024-10-01LATE REC'DLATE REC'DProject Late
Sheet1
Cell Formulas
RangeFormula
G2:G11,G14:G17G2=IF(C2-B2<60,"LATE REC'D","On Time")
H2:H11,H14:H17H2=IF(C2-B2<45,"LATE REC'D","On Time")
I2:I11,I14:I17I2=IF(F2<C2,"Projected On Time","Project Late")
 
Upvote 0
Solution

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

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