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.