Math For Spacing Out Three-Day Weekends, But Accounting for Holidays
January 13, 2017 3:28 PM   Subscribe

I'm a big honkin' geek, and I would like to figure out an Excel formula I could reuse from year to year to space out my vacation day choices -- but including holidays, which would prevent even distribution. How can I still get as close as possible? More inside.

So, I'd like to be able to space out three- or four-day weekends across a year an Excel formula, but I can't seem to wrap my head around how to structure the formula.

The issue is that it is not just dividing the number of weeks in a row by my number of vacation days (or half my vacation days, if I do 4-day weekends) ... because as an office we have certain weekends off as holidays ... Memorial Day, July 4th, Labor Day, etc. I'd like to include those when considering the "spacing", but doing so will throw off the even distribution of the dates -- so I'm not sure how to set up the math on this.

posted by WCityMike to Science & Nature (10 answers total) 3 users marked this as a favorite
Count your holidays, count your vacation days. Find total. Divide into 52. This tells you how often you get a long weekend. Pick a holiday and count forward to the next long weekend and so forth. But this doesn't involve excel, not sure if I'm missing something.
posted by mai at 3:42 PM on January 13, 2017

i think the fact that you cannot distribute the holidays evenly means that a strict formula won't work.
you will need to get a calendar and eyeball it a bit.
i would add holidays+vacation days and divide by 12.
that tells me how many off days I can afford each month.
then i would get a calendar and visually space them out.
posted by calgirl at 4:20 PM on January 13, 2017

Print out a compact calendar (look for the PDF links in the right column of the site). Circle the days you already get off. Figure out how many weekends without days off that leaves. Take your vacation days and distribute them among the remaining weekends.
posted by Wild_Eep at 4:44 PM on January 13, 2017

I have a pretty good idea how I'd do it with a full-blown programming language, and I think trying to cram that much logic into an Excel formula would be exceedingly painful. If I were doing it for myself I'd probably just do what calgirl suggested, because I don't see much advantage in distributing vacation days in a mathematically perfect way.
posted by shponglespore at 5:26 PM on January 13, 2017

Response by poster: Thanks, everyone.
posted by WCityMike at 6:01 PM on January 13, 2017

Best answer: Not too tricky in Excel, but not easy, either. I've done a rough and ready version in Excel here, with some ugly formulas and array formulas in there, but someone who knows Excel could well work it out. Obviously cell J2 (named HolidayCount) is the place to put in the number of holidays you're trying to fit in.
posted by ambrosen at 6:12 PM on January 13, 2017

Response by poster: Thank you, ambrosen, this is beautiful.
posted by WCityMike at 6:56 PM on January 14, 2017

Response by poster: Ambrosen, just out of curiousity, if the holiday count goes below 16, the calculated holidays DIV/0 out. Any idea why?
posted by WCityMike at 7:05 PM on January 14, 2017

Response by poster: Never mind -- I believe I realize why. The holiday count includes the fixed holidays as well as "vacation days".
posted by WCityMike at 7:19 PM on January 14, 2017

My pleasure. There's a few bugs in the methodology, because it was far too late in the evening when I started working on it, but it was a nice little puzzler.

And I'd have preferred to have done something a little cleverer with the thing that kept track of the most recently used row. Also, I've just discovered named formulas, and I'd have loved to have tried to use those to do the heavy lifting.
posted by ambrosen at 3:18 PM on January 15, 2017

« Older Is anti-glare coating on glasses worth it?   |   What movie about the Manhattan Project did I watch... Newer »
This thread is closed to new comments.