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
|