No of Days between two dates and offset formula



I want to find the no of days b/w two days it should consider saturday as
half day holiday and sunday as fully day holiday . and any holidays in that

I need ans at c1= 11 . In between 1 and 15, date 14 is holiday.


1 1-Jan-10 15-Jan-10

One more thing

In a1 = Project Start Month , b1= Jan-10
In a2 = Project Duration , b2= 10 ( which may change depend upon the project)

I need a formula so that from it shows
a6= Feb-10
a7= Mar-10 ...... so on depend upon the value in B2 cell


Not sure I can help with needed formula in C1. I think I could do it with a
User Defined Function (VBA code), but someone may come along with an answer.

Meanwhile, for the formulas needed starting at A5, try this
Fill down the sheet as far as you care to go. It will show a blank cell
starting when you get beyond the # of months duration in B2. The 'secret' is
in the
ROW() - ROW(A$5) parts. ROW(A$5) is always going to evaluate to 5. Since
we are starting at row 5, current row - 5=0, when the formula moves to row 6,
then (currentrow=6)-5 = 1 so we get the next month.
You could write it as
But I think using the ROW(A$5) gives someone reading the formulas months
from now a little clue as to what is going on - they should see that row 5 is
the first row with the formula in it.


For the formula needed in C1, you could look at the NETWORKDAYS() function.
This is part of an Add-In called the Analysis ToolPak. You'll need to
'install' that add-in if you haven't already to use NETWORKDAYS(). Search
Excel Help for NETWORKDAYS() for all the details.

Basically NETWORKDAYS() gives you a total of the days between 2 days but
automatically considers both Saturday and Sunday as non-work days. So you
would have an 'error' of 0.5 days per week.
With A1 = 1-Jan-2010 and B1 = 15-JAN-2010, then
=NETWORKDAYS(A1,B1) gives you a result of 11, as you asked for. But I think
that may be a 'special' case because of Jan 01.
If you enter dates of 1/4/2010 and 1/18/2010, then the result is 11, and I'm
thinking that you really expect to see 12 in this case: the formula has
subtracted 2 whole days for the two Saturdays, but you only want to subtract
2x0.5 or 1 day per 2 Saturdays.


Dear JLatham,
Thank you very much it worked Perfectly. It saved my time lot.


Hi JLatham,
Thank you very much.

I tried the networkdays() formula but it consider both sat and sun as
holiday . but i require sat as half day.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question