Calculate Number Of Business Days Between Two Dates and Allocate Them Across Week Ending Dates

T

tyrese215

New Member
Joined
Feb 25, 2009
Messages
33
Hi Excel Experts,

I have a problem that i cant seem to figure out.

I have a Huge spreadsheet with start and end dates in column A & B respectively.

cell D1 to Z1 I have Weekending Dates Starting from Weekending 5/Feb/10

My problem is when the user enters in a start date of say 1/feb/2010 and an end date of 10/feb/10 in cells A2 and B2 respectively, i want a formula in cells D2 to Z2 that will calculate How many business days of this Start and End Date Fall within the weekending date. This then needs to be dragged down..

So in the example above, if the start date is 1/Feb/2010 and an end date is 10/feb/10 in cells A2 and B2. Than in Cells D2 which has the Weekending Title of 5/Feb/10 should auto calculate the result of 5. Then in cell E2 which has the weekending title of 12/Feb/10 should auto calculate the result of 3. Because there are no business days for F2 which has the weekending title of 19/Feb/10, that are triggered by the start and end date above, than this should auto calculate 0.

And so forth..

I tried everything i know but i cant get this to work..

Pleeeassee help meeeeee..

thankyou soooooo much everyone.. I look forward to your formula solutions
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Hopefully this is enough to work with. It seemed to work, but I didn't
think it all the way through and it might not be the best solution:


D2 would need to be different from the rest I think:
Code: 
=IF($A$2 < D1,NETWORKDAYS($A$2,D1),0)

Then in E2:
Code: 
=IF($A$2 < E1,NETWORKDAYS($A$2,E1)-IF(E1 > $B$2,NETWORKDAYS($B$2,E1)-1,0)-SUM($D$2:D2),0)

Fill it to the right and all of them should work.

NOTE: I'm having trouble with the formatting when I type "less than" signs, so you'll need to take spaces out that are around those.
 
Last edited:
Upvote 0
If you use MAX you can avoid an IF function, i.e. in D2 copied across and down

=MAX(0,NETWORKDAYS(MAX(D$1-6,$A2),MIN(D$1,$B2)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Revenue Allocation by Week
Replies
3
Views
273
ChetanPuri
C
R
  • Solved
Subtracting all calendar days, but end date cannot fall on a weekend or holiday. If it does, the end date shifts to the preceding work day.
Replies
8
Views
325
Romy
R
C
  • Question
Need a formula for Business Hours Mon-Fri 8AM - 5PM between two dates and times!!
Replies
3
Views
54
etaf
etaf
Sweedler
  • Solved
Time between two dates
Replies
8
Views
313
Joe4
Joe4
R
  • Question
Calculating future date excluding Public Holiday, Weekends and Backdate specific date
Replies
1
Views
114
etaf
etaf

Forum statistics

Threads
1,215,375
Messages
6,124,581
Members
449,174
Latest member
chandan4057

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top