Appreciate any help in advance
Having trouble coming up with a formula for column J (Time at Depot) to return number of days (rounded up) it takes for an item to get delivered based on the following scenario below:
using cell J2 as reference
IF I2="IMC", return C2
IF I2="AMC", return E2
IF I2="WNM" & A2="C4", H2-B2 OR IF I2="WNM" & A2="C6", H2-D2 [if B2 or D2 blank, return "no scan"]
IF I2="WKM", H2-D2
IF I2="AKL delivery", H2-D2
IF I2="CHC delivery", H2-F2 [if F2 blank, return "no scan"]
IF I2="TRG", H2-D2
IF I2="CHM", return G2 [if G2 blank, return "no scan"]
A | B | C | D | E | F | G | H | I | J | |
1 | Product | Exit IMC | Time at IMC | Exit AMC | Time at AMC | Exit CHM | Time at CHM | Delivered Date | Delay Point | Time at Depot |
2 | C6 | 10-08-2017 11:22:00 AM | 21hrs | 10-08-2017 8:35:00 PM | 6hrs | 12-08-2017 5:50:00 AM | 12-08-2017 | IMC | ||
3 | C6 | 06-08-2017 10:29:00 AM | 2days | 08-08-2017 4:23:00 AM | 2days | 10-08-2017 | AMC | |||
4 | C6 | 09-08-2017 4:46:00 PM | 4hrs | 09-08-2017 8:30:00 PM | 2hrs | 11-08-2017 | WNM | |||
5 | C4 | 10-08-2017 3:02:00 PM | 23hrs | 12-08-2017 | WNM | |||||
6 | C6 | 13-08-2017 1:06:00 PM | 2days | 14-08-2017 10:02:00 PM | 2days | 16-08-2017 | WKM | |||
7 | C6 | 06-08-2017 1:06:00 PM | 3hrs | 08-08-2017 4:08:00 AM | 2days | 09-08-2017 | AKL delivery | |||
8 | C4 | 07-08-2017 11:39:00 AM | 4hrs | 08-08-2017 5:50:00 AM | 7hrs | 09-08-2017 | CHC delivery | |||
9 | C6 | 08-08-2017 10:54:00 AM | 22hrs | 08-08-2017 9:45:00 PM | 8hrs | 10-08-2017 | TRG | |||
10 | C6 | 13-08-2017 11:03:00 AM | 2days | 14-08-2017 11:48:00 PM | 2days | 16-08-2017 6:02:00 AM | 21hrs | 17-08-2017 | CHM |
<tbody>
</tbody>