Hi guys,
I have a huge table with many columns and rows. Then in another sheet I have certain values that I need to use to calculate dates, so I need to
Example of values that I need to use for my calculation:
Customer: SZ
Final date: 10/07/2020 0:00
Transportation lead time: 96:00:00 (Hours) = 4 days
Initial date: ?
With this data I need to read the header (Row 1) and find the customer, then read the Final date from Column A and calculate 4 days backwards, however, in my table I have zeros and ones, this means 0 are invalid dates and 1 valid dates, so, the calculation should only be done against the valid days (ones only) and the Final date should not be count as part of the 4 days. For this example, when I take the Customer SZ and final date 10/07/2020 0:00 and count 4 days backwards the date return should be 09/30/2020 0:00.
Customer: SZ is located in E1
Final date: 10/07/2020 0:00 is located in A8
10/07/2020 - 4 days = 10/03/2020 however 10/04 and 10/03 are invalid dates and 10/07 should not count, so the calculation should bring 09/30/2020 0:00 as the Initial date.
Appreciate any assistance with this calculation.
I have a huge table with many columns and rows. Then in another sheet I have certain values that I need to use to calculate dates, so I need to
Example of values that I need to use for my calculation:
Customer: SZ
Final date: 10/07/2020 0:00
Transportation lead time: 96:00:00 (Hours) = 4 days
Initial date: ?
With this data I need to read the header (Row 1) and find the customer, then read the Final date from Column A and calculate 4 days backwards, however, in my table I have zeros and ones, this means 0 are invalid dates and 1 valid dates, so, the calculation should only be done against the valid days (ones only) and the Final date should not be count as part of the 4 days. For this example, when I take the Customer SZ and final date 10/07/2020 0:00 and count 4 days backwards the date return should be 09/30/2020 0:00.
Customer: SZ is located in E1
Final date: 10/07/2020 0:00 is located in A8
10/07/2020 - 4 days = 10/03/2020 however 10/04 and 10/03 are invalid dates and 10/07 should not count, so the calculation should bring 09/30/2020 0:00 as the Initial date.
Appreciate any assistance with this calculation.
| A | B | C | D | E | F | G | H | I | J | K | |
1 | Date | SV | SX | SY | SZ | T4 | T5 | TH | TT | TW | TZ |
2 | 10/1/2020 0:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
3 | 10/2/2020 0:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 |
4 | 10/3/2020 0:00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
5 | 10/4/2020 0:00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
6 | 10/5/2020 0:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
7 | 10/6/2020 0:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
8 | 10/7/2020 0:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
9 | 10/8/2020 0:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
10 | 10/9/2020 0:00 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 0 |
11 | 10/10/2020 0:00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
12 | 10/11/2020 0:00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
13 | 10/12/2020 0:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
14 | 10/13/2020 0:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
15 | 10/14/2020 0:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
16 | 10/15/2020 0:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
17 | 10/16/2020 0:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 |
18 | 10/17/2020 0:00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
19 | 10/18/2020 0:00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
20 | 10/19/2020 0:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
21 | 10/20/2020 0:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
22 | 10/21/2020 0:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
23 | 10/22/2020 0:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
24 | 10/23/2020 0:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 |
25 | 10/24/2020 0:00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
26 | 10/25/2020 0:00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
27 | 10/26/2020 0:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
28 | 10/27/2020 0:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
29 | 10/28/2020 0:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
30 | 10/29/2020 0:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
31 | 10/30/2020 0:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 |
32 | 10/31/2020 0:00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
