Edit to clarify: the date returned MUST be less than 24 months from the current date because this is an expiration date, and the items must expire at the correct time unless renewed. Another way of putting it is that the date needs to be as close to a 2-year renewal cycle as possible without going over 2 years.
Original post: Good morning! I've been sifting through Google and getting nowhere, so I'm hoping I can get a hand with creating a formula. I need to create a single formula which doesn't reference other cells because I'm hoping to limit the number of errors by other users :)
What I need: find next instance of 03/31 OR 09/30 which is greater than 18 months but less than 24 months from TODAY.
Based on my phrasing above, I feel like some combination of TODAY, OR, <, >, and a month/day identifier would give me what I'm looking for, but I'm having difficulty with the order of operations. Currently my coworkers are using a table (below) to manually calculate these dates and it is tedious.
- April - Sept Odd = March Next Odd
- April - Sept Even = March Next Even
- Oct Even - Mar Odd = Sept Next Even
- Oct Odd - Mar Even = Sept Next Odd
Thanks in advance for any input, and I'll edit this if I come up with a rough formula after work!