What are the dates for the 9th and 20th business day for the rest of this year and all of next year?
September 14, 2012 1:36 PM Subscribe
I need to quickly figure the 9th and 20th business days of each month for the rest of 2012 and all of 2013. Preferably not in a manual fashion.
I've checked online, and all the calculators are for calculating the number of days between two dates.
Excel has a WORKDAY function, but also seems to be designed to figure out how many business days are between a specified start and end date.
What I need to find out is what the dates for the 9th and 20th business day are for each month for the rest of 2012 and for all of 2013. I'd prefer not to have to look off a calendar and do it manually.
I've checked online, and all the calculators are for calculating the number of days between two dates.
Excel has a WORKDAY function, but also seems to be designed to figure out how many business days are between a specified start and end date.
What I need to find out is what the dates for the 9th and 20th business day are for each month for the rest of 2012 and for all of 2013. I'd prefer not to have to look off a calendar and do it manually.
workday from the 1st og each month? btw dont forget holidays in a lookup table.
posted by Yowser at 1:45 PM on September 14, 2012
posted by Yowser at 1:45 PM on September 14, 2012
Best answer: I did this in Excel:
* put 9/14/12 in A 1
* in A2 used the formula =EOMONTH(A1,1) which returns 10/31/12
* in B2 =WORKDAY(A2,9)
* in C3 =WORKDAY(A2,20)
* drag down those three formulas to figure out for the rest of the months
That seems to work.
posted by mullacc at 1:46 PM on September 14, 2012
* put 9/14/12 in A 1
* in A2 used the formula =EOMONTH(A1,1) which returns 10/31/12
* in B2 =WORKDAY(A2,9)
* in C3 =WORKDAY(A2,20)
* drag down those three formulas to figure out for the rest of the months
That seems to work.
posted by mullacc at 1:46 PM on September 14, 2012
Best answer: I started doing this manually because it seemed like fun. I just noticed that December 2012 will not have a 20th business day if both December 24 and December 25 are considered holidays like they are at my company. December 31 is also a holiday at my company, but that would mark Business Day 19 YMMV.
In the meantime:
October 12, 29 (Columbus Day)
November 13, 30 (Thanksgiving, the Day After)
December 13 (Christmas Eve, Christmas)
Also as an afterthought, I though it might be cool to design a business day calendar, so I Googled and it seems it already exists here and here. It would be cool if you could see the Day Count/Days Remaining like on my At A Glance Desk Calendar. So I might make one that uses the other two as a base.
posted by alice ayres at 2:01 PM on September 14, 2012
In the meantime:
October 12, 29 (Columbus Day)
November 13, 30 (Thanksgiving, the Day After)
December 13 (Christmas Eve, Christmas)
Also as an afterthought, I though it might be cool to design a business day calendar, so I Googled and it seems it already exists here and here. It would be cool if you could see the Day Count/Days Remaining like on my At A Glance Desk Calendar. So I might make one that uses the other two as a base.
posted by alice ayres at 2:01 PM on September 14, 2012
Best answer: Jan 2013 - 14, 30 (New Year's, MLK, Jr. Day)
Feb 2013 - 13 (President's Day)
March 2013 - 13, 28
April 2013 - 11, 26
May 2013 - 13, 29 (Memorial Day)
June 2013 - 13, 28
July 2013 - 12, 29 OR 15, 21 (depends on if the day after 4th of July is given as a holiday by your company)
August 2013 - 13, 28
Sept 2013 - 13, 30
Oct 2013 - 14, 29
Nov 2013 - 13 (Thanksgiving, the Day After)
Dec 2013 - 12 or 12, 30 or 12, 31 (depends on whether you get Christmas Eve or day after Christmas off, and then if you also get New Year's Eve off)
As I was doing this, it occurred that you might have just wanted the formulaic method for doing this rather than the answer. Or that it's not really about working days, so my points about company holidays might be off.
Let me know if there are any caveats, and I can recalculate since I wrote in the day counts on my calendar.
posted by alice ayres at 2:25 PM on September 14, 2012
Feb 2013 - 13 (President's Day)
March 2013 - 13, 28
April 2013 - 11, 26
May 2013 - 13, 29 (Memorial Day)
June 2013 - 13, 28
July 2013 - 12, 29 OR 15, 21 (depends on if the day after 4th of July is given as a holiday by your company)
August 2013 - 13, 28
Sept 2013 - 13, 30
Oct 2013 - 14, 29
Nov 2013 - 13 (Thanksgiving, the Day After)
Dec 2013 - 12 or 12, 30 or 12, 31 (depends on whether you get Christmas Eve or day after Christmas off, and then if you also get New Year's Eve off)
As I was doing this, it occurred that you might have just wanted the formulaic method for doing this rather than the answer. Or that it's not really about working days, so my points about company holidays might be off.
Let me know if there are any caveats, and I can recalculate since I wrote in the day counts on my calendar.
posted by alice ayres at 2:25 PM on September 14, 2012
This thread is closed to new comments.
So for January, you'd do =workday(31/12/2012,9), though it would be easiest just to have a column of "last day in month", one of workday 9, one of workday 20.
Quick fiddling in Excel suggests this looks right.
posted by jeather at 1:44 PM on September 14, 2012