Good evening, all! I'm a new user to VBA with a complicated question.
I have a delivery schedule sheet where a daily delivery schedule number gets spread to an hourly schedule at the begging of the day. For the hours 11 to 20 the D columns gets multiplied by 0.03167. The hours 21 and 8 get multiplied by 0.04168 and hours 22-7 and 9-10 get multiplied by 0.05. The Delivery schedule can be changed at any time of the day (multiple times). If a new delivery schedule is entered (J2) the new value is spread only to the remainder of the hours, starting with the effective hour, as specified by the user. The user enters the effective hour for the spreading in (J1)
For example, if the daily schedule was set to 1400 then each hour would be spread based on the above formulas. If the schedule was revised to 1700 at 5:00 the additional 696 would be spread across the remaining 6 hours.
Currently the revised schedule column (E) is manually entered. Would it be possible to automate column E ? Thanks so much in advance!
I have a delivery schedule sheet where a daily delivery schedule number gets spread to an hourly schedule at the begging of the day. For the hours 11 to 20 the D columns gets multiplied by 0.03167. The hours 21 and 8 get multiplied by 0.04168 and hours 22-7 and 9-10 get multiplied by 0.05. The Delivery schedule can be changed at any time of the day (multiple times). If a new delivery schedule is entered (J2) the new value is spread only to the remainder of the hours, starting with the effective hour, as specified by the user. The user enters the effective hour for the spreading in (J1)
For example, if the daily schedule was set to 1400 then each hour would be spread based on the above formulas. If the schedule was revised to 1700 at 5:00 the additional 696 would be spread across the remaining 6 hours.
Currently the revised schedule column (E) is manually entered. Would it be possible to automate column E ? Thanks so much in advance!
| SchedulePattern.xlsx | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| C | D | E | F | G | H | I | J | |||
| 1 | Time | Total scheduled Delivery (original) | Revised Schedule | Actuals | VAR | Effective Hour | 5:00 | |||
| 2 | 1400 | 1700 | New Delivery Schedule | 1700 | ||||||
| 3 | 11:00 | 44 | 44 | 50 | 6 | Revised - Delivered | 696 | |||
| 4 | 12:00 | 44 | 44 | 50 | 6 | |||||
| 5 | 13:00 | 44 | 44 | 50 | 6 | |||||
| 6 | 14:00 | 44 | 44 | 54 | 10 | |||||
| 7 | 15:00 | 44 | 44 | 48 | 4 | |||||
| 8 | 16:00 | 44 | 44 | 48 | 4 | |||||
| 9 | 17:00 | 44 | 44 | 48 | 4 | |||||
| 10 | 18:00 | 44 | 44 | 48 | 4 | |||||
| 11 | 19:00 | 44 | 44 | 48 | 4 | |||||
| 12 | 20:00 | 44 | 44 | 48 | 4 | |||||
| 13 | 21:00 | 58 | 71 | 68 | 10 | |||||
| 14 | 22:00 | 70 | 70 | 68 | -2 | |||||
| 15 | 23:00 | 70 | 70 | 68 | -2 | |||||
| 16 | 0:00 | 70 | 70 | 68 | -2 | |||||
| 17 | 1:00 | 70 | 70 | 68 | -2 | |||||
| 18 | 2:00 | 70 | 70 | 68 | -2 | |||||
| 19 | 3:00 | 70 | 70 | 68 | -2 | |||||
| 20 | 4:00 | 70 | 70 | 68 | -2 | |||||
| 21 | 5:00 | 70 | 116 | 120 | 46 | |||||
| 22 | 6:00 | 70 | 116 | 120 | 46 | |||||
| 23 | 7:00 | 70 | 116 | 120 | 46 | |||||
| 24 | 8:00 | 58 | 116 | 120 | 58 | |||||
| 25 | 9:00 | 70 | 116 | 100 | 46 | |||||
| 26 | 10:00 | 70 | 116 | 100 | 46 | |||||
| 27 | 1400 | 1700 | 1716 | |||||||
Summer | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E2 | E2 | =J2 |
| D3:E12 | D3 | =$D$2*0.03167 |
| D13:E13 | D13 | =D2*0.04168 |
| D21:D23,D14:E20 | D14 | =$D$2*0.05 |
| E21:E26 | E21 | =$J$3/6 |
| D24 | D24 | =D2*0.04168 |
| D25 | D25 | =D2*0.05 |
| D26 | D26 | =D2*0.05 |
| J3 | J3 | =E2-(SUM(E3:E20)) |
| G3:G20 | G3 | =F3-D3 |
| G21:G26 | G21 | =E21-D21 |
| D27:F27 | D27 | =SUM(D3:D26) |
