No of Days between two dates and offset formula

[vmohan1978]

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
week

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

A B C D E
F G

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
a5=Jan-10
a6= Feb-10
a7= Mar-10 ...... so on depend upon the value in B2 cell

 

[JLatham]

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
=IF(ROW()-ROW(A$5)+1<=B$2,DATE(YEAR(B$1),MONTH(B$2)+ROW()-ROW(A$5),DAY(B$1)),"")
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
=IF(ROW()-5+1<=B$2,DATE(YEAR(B$1),MONTH(B$2)+ROW()-5,DAY(B$1)),"")
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.

 

[JLatham]

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.

 

[vmohan1978]

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

 

[vmohan1978]

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.