# Overlapping Dates

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.

=MIN(C5,B5-IF(B5>\$D\$2,DATE(YEAR(B5),MONTH(\$D\$2),DAY(\$D\$2)),IF(B5>\$D\$1,DATE(YEAR(B5),MONTH(\$D\$1),DAY(\$D\$1)),DATE(YEAR(B5)-1,MONTH(\$D\$2),DAY(\$D\$2)))))

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

=MIN(C5,B5-DATE(YEAR(B5),11,1))

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.

Posted in Uncategorized

## One thought on “Overlapping Dates”

1. Reuvain says:

Dick, It seems that the formula is not working for row 7. It is returning the number of cycle days NOT in winter instead of the number of cycle days that ARE in winter.

Posting code? Use <pre> tags for VBA and <code> tags for inline.