Dear All
Please advise how to calculate A to B as below where A is Day 0 and Weekend (Sat and Sun) is calculated as 1 Working Day.
A = 11/13/2007
B = 11/21/2007
or A = 08/31/2007
B = 10/24/2007
We are puzzeled on this since last 7 days because the Weekend - Sat and Sun need to be considered as 1 Week Day.
Appreciate your help.
SpeakBones
I have to give props to Chuck Pearson for the array formula that finds the Number of Mondays (this can be adjusted to any day you wish) between dates.
Here is the formula that I used. It is an array formula so you will need to use Ctl Shft Enter to enter it.
The assumptions are that the start date is in cell A2 and the End date is in Cell B2. The NETWORKDAYS function will give you all weekdays in the absence of a holiday arguemnt. The two sum formulas count the number of Saturdays and Sundays and divides them by 2 so that effectively they equal 1 day.
=NETWORKDAYS(A2,B2)+SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=1,1,0))/2+SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=7,1,0))/2
---------------------------------------------------
ONLY APPLIES TO VBA RESPONSES WHERE APPROPRIATE
To insert code into the VBE (Visual Basic Editor)
- Copy the code.
- Open workbook to paste code into.
- Right click any worksheet tab, select View Code
- VBE (Visual Basic Editor) opens to that sheets object
- You may change to another sheets object or the This Workbook object by double clicking it in the Project window
- In the blank space below the word "General" paste the copied code.
What happens if the date range starts or ends on a weekend?
I'm assuming you're only interested in date ranges which start and end on weekdays. If start date is in A2 and end date in B2 try
=B2-A2-INT((WEEKDAY(A2)+B2-A2-1)/7)
The formula I posted above is a variation on this formula which gives Mondays in a periodOriginally Posted by Excelenator
=INT((WEEKDAY(A2-2)+B2-A2)/7)
The 2 can be replaced with other numbers (1=sun to 7=sat) to count other days
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
speakbones 
Register To Reply
Bookmarks