LinkBack URL
About LinkBacks
Hello,
Is there a formula that counts the number of workdays between two dates, including Saturdays and excluding holidays?
Workdays = Mon - Sat
Holidays = Custom list
My Excel book only gives the formula that excludes Saturdays.
Many thanks.
Hello,
Is there a formula that counts the number of workdays between two dates, including Saturdays and excluding holidays?
Workdays = Mon - Sat
Holidays = Custom list
My Excel book only gives the formula that excludes Saturdays.
Many thanks.
Here are a couple of previous threads that covered a similar topic
http://www.excelforum.com/showthread.php?t=577071
http://www.excelforum.com/showthread.php?t=593868
Please Read Forum Rules Before Posting
Wrap VBA code by selecting the code and clicking the # icon or Read This
How To Cross Post politely
Top Excel links for beginners to Experts
If you are pleased with a member's answer then use the Scales icon to rate it
If my reply has assistedor failed to assist you
I welcome your Feedback.
I tried this formula from the link you sent me. Althouht it works well when adding a number to a date to get another date, it doesn't calculate the number of workdays between two dates.
For example,
Cell A1 = 12/01/06
Cell A2 = 12/31/06
Cell A3 contains a formula that subtracts the two dates ( 12/31/06 minus 12/01/06 ) and gives the answer: 25 ,because that's how many workdays are in December 2006 (including Saturdays, and excluding holiday).
Is there a solution for this scenario? I'm stuck. Or am I misunderstanding the forum threads?
Thank a million.
Last edited by Sean Anderson; 04-03-2007 at 09:16 PM.
Heres my thought. Use the networkdays to calculate mon through friday excluding the holidays. Have another tab that has all of the dates in the year, and use the weekday formula to show which ones are saturdays, and then do a count if between your date ranges and if it is a saturday, and add that back into the previous workday formula you created first. Not pretty but it would do what you want it to.
Following gregvb's idea:
say in cell a4: =networkdays(a2,b2,holidays) (see correct syntax in XL)
say in cell a5 : =SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))
result in a6 =a4+a5
A2 contains the beginning date of the interval
B2 contains the ending date of the interval
C2 contains the day-of-week number (1=Sunday, 2=Monday,...,7=Saturday)
First part calculates number of workdays minus weekends and holidays
The second part (entered as an array formula) calculates the number of saturdays (when c2=7). It was extracted from this link:http://cpearson.com/excel/DateTimeWS.htm
( you will need the Analysis toolpak installed for the networkdays function to work)
Thanks guys, that did the trick. I'm in the dark as to how part of the formula actually works under the hood, but it works, and that's the important thing.
SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))
Last edited by Sean Anderson; 04-12-2007 at 12:24 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks