Calcualtion days in month from 2 dates
 

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

Calcualtion days in month from 2 dates
 

Start date in F1, end date in F2.
Use two columns. In the first you can put the starting day of each
month. You can format to mmm-yy if you want to not show the date. Say
these are in A2:A13.
In B2:
=SUMPRODUCT((ROW(INDIRECT(F1&":"&F2))=A2)*(ROW(IN DIRECT(F1&":"&F2))<DATE(YEAR(A2),MONTH(A2)+1,1)))

HTH
Kostis Vezerides


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

Calcualtion days in month from 2 dates
 

Total for March =DAY(EOMONTH(A1,0))-DAY(A1)
Total for April =DAY(EOMONTH(A1,1))
Total for May =DAY(EOMONTH(A2,0))-DAY(A2)

You need an Analysis ToolPak under Tools Add-Ins


"phocused" wrote:

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

Calcualtion days in month from 2 dates
 

vezerid,

Fantastic, thankyou very much.... works a treat.

Rgds Paul

"vezerid" wrote:

Start date in F1, end date in F2.
Use two columns. In the first you can put the starting day of each
month. You can format to mmm-yy if you want to not show the date. Say
these are in A2:A13.
In B2:
=SUMPRODUCT((ROW(INDIRECT(F1&":"&F2))=A2)*(ROW(IN DIRECT(F1&":"&F2))<DATE(YEAR(A2),MONTH(A2)+1,1)))

HTH
Kostis Vezerides


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

Calcualtion days in month from 2 dates
 

Guys,

I wonder if i can impose on you just a little bit more. I wont have an end
date in all cases until the case is closed. In these instances i would like
to do the calculation on todays date versus the start date and then if the
end date is added then against the end date. I have tried to include
Vezerid's calculation in a nested if but without much sucess. Any ideas????

rgds Paul

"Teethless mama" wrote:

Total for March =DAY(EOMONTH(A1,0))-DAY(A1)
Total for April =DAY(EOMONTH(A1,1))
Total for May =DAY(EOMONTH(A2,0))-DAY(A2)

You need an Analysis ToolPak under Tools Add-Ins


"phocused" wrote:

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

Calcualtion days in month from 2 dates
 

Paul,
thanks for the feedback. The new formula will work identically, with
the same layout. If end date is blank then it will fill up the months
until TODAY().

=IF(F2<"",SUMPRODUCT((ROW(INDIRECT(F1&":"&F2))=A 2)*(ROW(INDIRECT(F1&":"&F2))<DATE(YEAR(A2),MONTH(A 2)+1,1))),SUMPRODUCT((ROW(INDIRECT(F1&":"&TODAY()) )=A2)*(ROW(INDIRECT(F1&":"&F2))<=TODAY()))


HTH
Kostis

phocused wrote:
Guys,

I wonder if i can impose on you just a little bit more. I wont have an end
date in all cases until the case is closed. In these instances i would like
to do the calculation on todays date versus the start date and then if the
end date is added then against the end date. I have tried to include
Vezerid's calculation in a nested if but without much sucess. Any ideas????

rgds Paul

"Teethless mama" wrote:

Total for March =DAY(EOMONTH(A1,0))-DAY(A1)
Total for April =DAY(EOMONTH(A1,1))
Total for May =DAY(EOMONTH(A2,0))-DAY(A2)

You need an Analysis ToolPak under Tools Add-Ins


"phocused" wrote:

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

Calcualtion days in month from 2 dates
 

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

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

Calcualtion days in month from 2 dates
 

Vezrid

Hi, I copied the calculation into my spreadsheet but initialy it didnt work,
seems that there is a comma missing which is now in there. However it still
doesnt seem to use the Today() value to calcualte, throws up a ref# error
when i remove the end date. I'm not sure what the problem is.

rgds Paul

"vezerid" wrote:

Paul,
thanks for the feedback. The new formula will work identically, with
the same layout. If end date is blank then it will fill up the months
until TODAY().

=IF(F2<"",SUMPRODUCT((ROW(INDIRECT(F1&":"&F2))=A 2)*(ROW(INDIRECT(F1&":"&F2))<DATE(YEAR(A2),MONTH(A 2)+1,1))),SUMPRODUCT((ROW(INDIRECT(F1&":"&TODAY()) )=A2)*(ROW(INDIRECT(F1&":"&F2))<=TODAY()))


HTH
Kostis

phocused wrote:
Guys,

I wonder if i can impose on you just a little bit more. I wont have an end
date in all cases until the case is closed. In these instances i would like
to do the calculation on todays date versus the start date and then if the
end date is added then against the end date. I have tried to include
Vezerid's calculation in a nested if but without much sucess. Any ideas????

rgds Paul

"Teethless mama" wrote:

Total for March =DAY(EOMONTH(A1,0))-DAY(A1)
Total for April =DAY(EOMONTH(A1,1))
Total for May =DAY(EOMONTH(A2,0))-DAY(A2)

You need an Analysis ToolPak under Tools Add-Ins


"phocused" wrote:

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

Calcualtion days in month from 2 dates
 

Were do i need to go to aquire the skills to do this myslef.

You're already there! Right here in these newsgroups.

Biff

"phocused" wrote in message
...
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

Calcualtion days in month from 2 dates
 

My apologies... I did not test the formula or its logic. This one is
tested and should work:

=IF($F$2<"",SUMPRODUCT((ROW(INDIRECT($F$1&":"&$F$ 2))=A2)*(ROW(INDIRECT($F$1&":"&$F$2))<DATE(YEAR(A 2),MONTH(A2)+1,1))),SUMPRODUCT((ROW(INDIRECT($F$1& ":"&TODAY()))=A2)*(ROW(INDIRECT($F$1&":"&TODAY()) )<=MIN(TODAY(),DATE(YEAR(A2),MONTH(A2)+1,1)))))

HTH
Kostis

phocused wrote:
Vezrid

Hi, I copied the calculation into my spreadsheet but initialy it didnt work,
seems that there is a comma missing which is now in there. However it still
doesnt seem to use the Today() value to calcualte, throws up a ref# error
when i remove the end date. I'm not sure what the problem is.

rgds Paul

"vezerid" wrote:

Paul,
thanks for the feedback. The new formula will work identically, with
the same layout. If end date is blank then it will fill up the months
until TODAY().

=IF(F2<"",SUMPRODUCT((ROW(INDIRECT(F1&":"&F2))=A 2)*(ROW(INDIRECT(F1&":"&F2))<DATE(YEAR(A2),MONTH(A 2)+1,1))),SUMPRODUCT((ROW(INDIRECT(F1&":"&TODAY()) )=A2)*(ROW(INDIRECT(F1&":"&F2))<=TODAY()))


HTH
Kostis

phocused wrote:
Guys,

I wonder if i can impose on you just a little bit more. I wont have an end
date in all cases until the case is closed. In these instances i would like
to do the calculation on todays date versus the start date and then if the
end date is added then against the end date. I have tried to include
Vezerid's calculation in a nested if but without much sucess. Any ideas????

rgds Paul

"Teethless mama" wrote:

Total for March =DAY(EOMONTH(A1,0))-DAY(A1)
Total for April =DAY(EOMONTH(A1,1))
Total for May =DAY(EOMONTH(A2,0))-DAY(A2)

You need an Analysis ToolPak under Tools Add-Ins


"phocused" wrote:

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