yogi_Subtract Row By Row From Given Dates Number Of Days Based On Specified Criteria

Google Spreadsheet   Post  #2219

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-07-2017

question by risen2connquer :

https://productforums.google.com/forum/#!topic/docs/64k4Q3E5UpM;context-place=mydiscussions
Need Formula To Calculate Number Of Days To Subtract From Date

I'm trying to figure out how to write a formula which would populate the cells in a column on my first DATES sheet by subtracting day values from a manually entered date in another column. The days to be subtracted are determined by manually entered PLACE values in the same row which correspond to varying numbers as defined on the second DATA page.

There are two conditions which determine whether the PLACE value is valid, however:

Rule 1: If a PLACE value on the DATES sheet is not the colour orange (or if its neighbouring STATUS cell contains data, whichever is easier for a formula to calculate), the days corresponding to this particular PLACE value are not considered.

Rule 2: If there is more than 1 instance of the same PLACE value in the same row (which conforms to Rule 1), only one instance of this value is considered.

Please see the attached example and let me know if something is not clear. I really appreciate any help with this!