Hello all. I am looking for two formulas to return specific dates.
For the first, I need a formula that will return the most recent 8th day of the month before today. If I use the current timeframe as an example, the formula would return 9/8/18 until 10/8/18 occurs, then it will show that date instead (thereby rolling forward one month). It would then hold that date until 11/8/18 occurs when it switches to that date, etc. Let's say this formula is in B1.
The second formula would be in B2 and is predicated on the date returned in B1. It will need to show the next occurring 7th day of the month. So using the example above, if B1 returns 9/8/18, this formula would return 10/7/18. If B1 returns 10/8/18, this one would return 11/7/18, and so on.
My research so far has only been able to find formulas for specific dates for the nth day of the *current* month, and has thus been ineffective since my timeframe straddles months. Any help is greatly appreciated.
For the first, I need a formula that will return the most recent 8th day of the month before today. If I use the current timeframe as an example, the formula would return 9/8/18 until 10/8/18 occurs, then it will show that date instead (thereby rolling forward one month). It would then hold that date until 11/8/18 occurs when it switches to that date, etc. Let's say this formula is in B1.
The second formula would be in B2 and is predicated on the date returned in B1. It will need to show the next occurring 7th day of the month. So using the example above, if B1 returns 9/8/18, this formula would return 10/7/18. If B1 returns 10/8/18, this one would return 11/7/18, and so on.
My research so far has only been able to find formulas for specific dates for the nth day of the *current* month, and has thus been ineffective since my timeframe straddles months. Any help is greatly appreciated.