I need help with the following spreadsheet.
It is to report On Time Delivery. I have set up as much as I can but to be honest I need help on the formula in column " G ".
The report is based on deliverys on the same day mostly within a 12 hour period.
This is what I am hoping to achieve/
1) If the time in column D is prior to the ETA but not prior to the ship time show in cell G2 as " On Time "
2) If the time in column D is prior to the ETA and prior to the ship time show in cell G2 as " Early "
3) If the time in column D is after the ETA show in cell G2 as " Late "
4) Regardless of what time is entered in column D, if a letter ( A, B, C etc. ) shows in column F show in cell G2 as " On Time "
The letters are used when a delivery will technically be late due but due to a reason such as customer NSF.
Any help would be appreciated.
It is to report On Time Delivery. I have set up as much as I can but to be honest I need help on the formula in column " G ".
The report is based on deliverys on the same day mostly within a 12 hour period.
This is what I am hoping to achieve/
1) If the time in column D is prior to the ETA but not prior to the ship time show in cell G2 as " On Time "
2) If the time in column D is prior to the ETA and prior to the ship time show in cell G2 as " Early "
3) If the time in column D is after the ETA show in cell G2 as " Late "
4) Regardless of what time is entered in column D, if a letter ( A, B, C etc. ) shows in column F show in cell G2 as " On Time "
The letters are used when a delivery will technically be late due but due to a reason such as customer NSF.
Any help would be appreciated.
Excel Workbook | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | WEEKLY SCHEDULE | SHIP TIME | ETA | ACTUAL | Diff | Comment | On Time / Late | ||
2 | Store 1 | 12:00 | 2:00 PM | 1:25 PM | -0:35 | On Time | |||
3 | Store 2 | 12:00 | 3:00 PM | 2:25 PM | -0:35 | On Time | |||
4 | Store 3 | 12:00 | 4:00 PM | 11:25 AM | -4:35 | A | On Time | ||
5 | Store 4 | 12:00 | 5:00 PM | 5:20 PM | 0:20 | Late | |||
6 | Store 5 | 12:00 | 6:00 PM | 6:20 PM | 0:20 | B | On Time | ||
7 | Store 6 | 12:00 | 7:00 PM | 7:20 PM | 0:20 | B | On Time | ||
8 | Store 7 | 12:00 | 8:00 PM | 11:00 AM | -9:00 | Early | |||
9 | Store 8 | 12:00 | 9:00 PM | 11:00 AM | -10:00 | C | On Time | ||
10 | Store 9 | 12:00 | 10:00 PM | 10:30 AM | -11:30 | Early | |||
11 | Store 10 | 8:00 AM | 7:00 AM | -1:00 | On Time | ||||
12 | Store 11 | 9:00 AM | 8:00 AM | -1:00 | On Time | ||||
13 | Store 12 | 10:00 AM | 9:00 AM | -1:00 | On Time | ||||
14 | Store 13 | 11:00 AM | 10:00 AM | -1:00 | On Time | ||||
15 | Store 14 | 12:00 PM | 12:00 PM | 0:00 | On Time | ||||
16 | Store 15 | 1:00 PM | 2:00 PM | 1:00 | Late | ||||
17 | Store 16 | 2:00 PM | 3:00 PM | 1:00 | Late | ||||
18 | Store 17 | 3:00 PM | 4:00 PM | 1:00 | Late | ||||
19 | Store 18 | 4:00 PM | 5:00 PM | 1:00 | Late | ||||
20 | Store 19 | 5:00 PM | 11:00 AM | -6:00 | On Time | ||||
21 | Store 20 | 6:00 PM | 12:00 PM | -6:00 | On Time | ||||
22 | Store 21 | 7:00 PM | 1:00 PM | -6:00 | On Time | ||||
23 | Store 22 | 8:00 PM | 2:00 PM | -6:00 | On Time | ||||
Weekly |
Last edited: