Find the nearest Christmas to a date

[lbbeurmann]

I am setting up a class schedule that needs to account for time off during
Christmas. The classes all take the same number of work days, but some have
a 2 week vacation in the middle for the Christmas holiday. Since I am
building a class schedule for several years in advance, I need find a way to
determine if a class, based on it's ending date, will take place during the
Christmas holiday so that I can add an additional 2 weeks to the class period
and adjust the starting date. I need a way to do this that is formula based,
so that as I add new classes, I don't need to figure out whether the class
will span the holiday, but that it will be automatically figured for me.
Thanks in advance for the help.

 

[vezerid]

A lot of info is missing for a complete solution. But, basically, to
test that Christmas is, say, less than 28 days before class end, and
if class end is in A2:

=IF(A2-DATE(YEAR(A2)-1,12,25)<28,"Christmas in", "no Christmas)

The condition is the core of your calculation. Now, if otherwise
starting date is 30 days before end date, the starting date could be
computed by:
=A2-30-IF(A2-DATE(YEAR(A2)-1,12,25)<28,14,0)

HTH
Kostis Vezerides