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
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

No of Days between two dates and offset formula
 

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.





"vmohan1978" wrote:

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

No of Days between two dates and offset formula
 

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" wrote:

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

No of Days between two dates and offset formula
 

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


"JLatham" wrote:

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.





"vmohan1978" wrote:

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

No of Days between two dates and offset formula
 

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.



"JLatham" wrote:

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" wrote:

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