Given a Bill Date and a Cycle Days, Kimberly wants to determine how many days in the cycle fall in Winter. For our purposes, Winter is defined as November 1st to April 30th. I put the season start dates in D1 and D2 and use this formula to find the difference.

It’s not as onerous as it might seem at first. Let’s start with the case of Bill Date after November 1, but before January 1. If we were restricted to that time period, the formula would be

Take the date in question and subtract the start of Winter. Take the smaller of that or the cycle time. Once you have that basic formula, it’s easy to expand it. There are three time frames that I care about: pre-May 1, post-Nov 1, and the time in between. If it’s after November 1, I use the formula above. If it’s after May 1, I use the formula above but use May 1 as the date. If it’s before May 1, I use November again, but the year prior. If the Cycle Days spans both dates, well, there’s trouble, so let’s call that a bug that we don’t care to fix.

You might think those days are one off. They match some sample data I was provided, so I assume it’s good to go. If you want the actual number of days from 1-Nov-2011 to 16-Nov-2011, you need to add 1 to the above formula.