Google-sheets – Need to identify when a specific amount of time has past given a specific span of time

dateformulasgoogle sheets

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.

Best Answer

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.

Related Topic