Calcualtion days in month from 2 dates
vezirid, Valko, both solutions worked equaly superbly. Were do i need to go
to aquire the skills to do this myslef. Excellent help.... thankyou both.
Rgds Paul
"T. Valko" wrote:
Start Date: 23/03/06
End Date: 19/05/06
So I will end up with a column total for march of 8days, april 30days,
may19days.
So, that means you do not want to count the start date but you do want to
count the end date. Otherwise, you would have 9 days for March.
Here's another way (no helper columns needed):
A2 = start date
B2 = end date or, if no end date has been entered the cell will be empty and
the calculations will be based on today's date:
D1 = header = Month/Year
E1 = header = Days
Enter this formula in D2:
=IF(DATE(YEAR(A$2),MONTH(A$2)+ROWS($1:1)-1,1)<IF(B$2="",TODAY(),B$2),TEXT(DATE(YEAR(A$2),MO NTH(A$2)+ROWS($1:1)-1,1),"mmmm
yyyy"),"")
Enter this formula in E2:
=IF(MAX(A$2,DATE(YEAR(A$2),MONTH(A$2)+ROWS($1:1)-1,1))<IF(B$2="",TODAY(),B$2),MIN(DATE(YEAR(A$2),MO NTH(A$2)+ROWS($1:2)-1,0),IF(B$2="",TODAY(),B$2))-MAX(A$2+(ROWS($1:1)=1),DATE(YEAR(A$2),MONTH(A$2)+R OWS($1:1)-1,1))+1,"")
Select both D2 and E2 then copy down until you get blanks.
Based on your sample dates the results will look like this:
.....................D....................E
1..........Month/Year..........Days
2..........March 2006............8
3..........April 2006.............30
4..........May 2006.............19
5...........................................
Biff
"phocused" wrote in message
...
I have a start date and an end date, what I would like to do is to use
these
2 dates to calculate the number of days per month. So for example
Start Date: 23/03/06
End Date: 19/05/06
What I want to do is to work out how many days this is in March, April,
May
respectively.
So I will end up with a column total for march of 8days, april 30days,
may19days.
any help would be gratefully accepted
|