mpatino

Board Regular
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.

ABCDEFGHIJK
1​
DateSVSXSYSZT4T5THTTTWTZ
2​
10/1/2020 0:00​
1111111111
3​
10/2/2020 0:00​
1111111110
4​
10/3/2020 0:00​
0000000000
5​
10/4/2020 0:00​
0000000001
6​
10/5/2020 0:00​
1111111111
7​
10/6/2020 0:00​
1111111111
8​
10/7/2020 0:00​
1111111111
9​
10/8/2020 0:00​
1111111111
10​
10/9/2020 0:00​
1111101110
11​
10/10/2020 0:00​
0000000000
12​
10/11/2020 0:00​
0000000001
13​
10/12/2020 0:00​
1111111111
14​
10/13/2020 0:00​
1111111111
15​
10/14/2020 0:00​
1111111111
16​
10/15/2020 0:00​
1111111111
17​
10/16/2020 0:00​
1111111110
18​
10/17/2020 0:00​
0000000000
19​
10/18/2020 0:00​
0000000001
20​
10/19/2020 0:00​
1111111111
21​
10/20/2020 0:00​
1111111111
22​
10/21/2020 0:00​
1111111111
23​
10/22/2020 0:00​
1111111111
24​
10/23/2020 0:00​
1111111110
25​
10/24/2020 0:00​
0000000000
26​
10/25/2020 0:00​
0000000001
27​
10/26/2020 0:00​
1111111111
28​
10/27/2020 0:00​
1111111111
29​
10/28/2020 0:00​
1111111111
30​
10/29/2020 0:00​
1111111111
31​
10/30/2020 0:00​
1111111110
32​
10/31/2020 0:00​
0000000000

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Replies
1
Views
66
Replies
0
Views
44
Replies
1
Views
42
Replies
4
Views
68
Replies
1
Views
198

1,127,573
Messages
5,625,586
Members
416,120
Latest member
B3nj1

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.

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

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