Need to identify when a specific amount of time has past given a specific span of time

Question

I have a start date and a payout date which is 70 days from the start date. Throughout the year I need to know when each time 70 days has past and when it does is that date between the start and ending of a given month.

For example, if the start date is 1/1/17 then 70 days from then is 3/12/17. On my calendar I'm looking to output data for the month of March because 3/12/17 falls in the month March.

I've written formulas to check in every month column.

Here's what I have so far:

 |   A    |   B    |   C    |   D    |
1| 1/1/17 |        |        |        |
2|        | 1/1/17 | 2/1/17 | 3/1/17 |
3|        |        |        | output |

• A1 = start date
• B2 = Jan
• C2 = Feb
• D2 = Mar
• D3 = where to output

=if(ROUND(MINUS(EOMONTH(D2,0),(A1+ROUND((EOMonth(D2,0)-A1)/70)*70)))>0,if(ROUND(MINUS(D2,A1))<ROUND((EOMonth(D2,0)-A1)/70)*70,"output",""),"")

This is working for some of the month slots but not all of them. I'm looking for any advice on what functions are best to use when measuring and calculating dates in Google Sheets.

Answer 1

In cell D3 I owuld put the following formula:

=if(EOMONTH($A1+70,0)=EOMONTH(D2,0),"Output","No report due")

I assumed the $A1 to make sure that it works for all columns.

The formula just compares the end of the month in the second row with the end of the month for the date in cell a1 + 70 days.

If the date is 1/1/2017 then 70 days later is 3/12/2017, so the end of that month is 3/31/2017. So the phrase Output appears when the current month has an end date equal to 3/31/2017.

It will work for all columns as long as they have a date in the 2nd row.

Answer 2

Here's the answer I came up with. I'm including extra features that were not part of my original question in case it helps anyone.

Some of the data is on a secondary sheet named "Details". The $ that precedes the letter and number of each remote variable ensures that the reference won't change if the formula is moved around on the sheet.

Starting at A37 I have listed vertically each letter of the alphabet plus the alphabet repeated with each letter preceded by "A". This was done to mimic the sequence along the horizontal axis in order to reference the letters to build references to cell coordinates.

=if(Details!$C$14=P2,sum(indirect("B4:"&indirect("A"&Details!$C$14+37)&"4")),if(Details!$C$14=O2,indirect(indirect("A"&Details!$C$14+1+37)&4),if(Q2<=Details!$C$14,if(ROUND(MINUS(EOMONTH(Q1,0),(Details!$C$12+ROUND((EOMonth(Q1,0)-Details!$C$12)/Details!$G$5)*Details!$G$5)))>0,if(ROUND(MINUS(Q1,Details!$C$12))<ROUND((EOMonth(Q1,0)-Details!$C$12)/Details!$G$5)*Details!$G$5,sum(Details!$I$3*Details!$I$4*Details!$D$3),""),""),"")))

• Details!$C$14 = the total number of months to display.
• Range B4:A(n) represents the horizontal coordinates to the last column (last month).
• Q2 contains a number in succession from 1 (starting at B2) to however many months are to be displayed as indicated by Details!$C$14.
• Q1 contains a date (eg. 4/1/17) representing the month of the given column
• Details!$C$12 = the start date for when to begin counting in successive blocks of 70 days.
• Details!$G$5 = 70 (number of days can be dynamic)

• sum(Details!$I$3*Details!$I$4*Details!$D$3) represent the output.

  ---

• Details!$C$14=P2 is performed to check whether we've reached the last month column.

• sum(indirect("B4:"&indirect("A"&Details!$C$14+37)&"4")) Adds up items accross 4th row starting from B4 until the last column as determined by referencing the vertical alphabet list mentioned above to the reference of the total number of months to be displayed.
• if(Details!$C$14=O2,indirect(indirect("A"&Details!$C$14+1+37)&4) Similar to above but does the same for the next column to the right.
• if(Q2<=Details!$C$14 check if we're in a column before the last column or at the last column.
• if(ROUND(MINUS(EOMONTH(Q1,0),(Details!$C$12+ROUND((EOMonth(Q1,0)-Details!$C$12)/Details!$G$5)*Details!$G$5)))>0 Subtracts the last day of the current month from the start date, divided by the number of number of days between each cycle, multiplied by the number of days in the cycle. This is key to knowing which month column the start day of the next cycle falls under by checking if it falls after the start of the current month.
• if(ROUND(MINUS(Q1,Details!$C$12))<ROUND((EOMonth(Q1,0)-Details!$C$12)/Details!$G$5)*Details!$G$5 checks if the current start of the month minus the start of the calendar is less than the last day of the current month minus the start of the calendar divided by the number of days in each cycle, divided by the number of days in the cycle. Along with the prior bullet point, this determines if the start date of the next cycle is within the outer limits of the days of the current month.
• sum(Details!$I$3*Details!$I$4*Details!$D$3) Represents the output to perform.