Counting Specific Number of Days across Multiple Months
 

I have a task which seems relatively easy at first. I have two dates
as inputs: a start date and a stop date that can vary between dates
and lenght of time. I have a header row with dates by month. I am
trying to write a formula that will tell me how many days between the
start and stop date are in each month. For example, if I start on Jan
30 and end on Feb 2nd of the same year, I will show 2 under the Jan
header and 2 under the Feb header. Sometimes it will go on for a
couple months, so if it is a complete months, say starts on Jan 30th
and Ends on Mar 2, I will need it say 2 under Jan, 28 under Feb, and 2
under March. I have tried to Dateif, but not sure if I am looking at
it correctly? Any suggestions would be most helpful. Thank you in
advance!

Counting Specific Number of Days across Multiple Months
 

One approach to achieve it is illustrated in this sample construct:
http://www.savefile.com/files/638369
Apportioning days within a date range under correct month cols.xls

Startdates in E3 down, Enddates in F3 down
1st of month dates (formatted as "mmm-yy") listed in L2 across, viz: Jan-07,
Feb-07, etc

Then in L3:
=IF(TEXT(L$2,"mmm-yy")=TEXT($E3,"mmm-yy"),DATE(YEAR(L$2),MONTH(L$2)+1,0)-$E3+1,IF(TEXT(L$2,"mmm-yy")=TEXT($F3,"mmm-yy"),$F3-DATE(YEAR(L$2),MONTH(L$2),1)+1,IF(AND(DATE(YEAR(L$ 2),MONTH(L$2),1)DATE(YEAR($E3),MONTH($E3),1),DATE (YEAR(L$2),MONTH(L$2),1)<DATE(YEAR($F3),MONTH($F3) ,1)),DAY(DATE(YEAR(L$2),MONTH(L$2)+1,0)),"")))

Copy L3 across/fill down as far as required. This will return the number of
days under each month's col as appropriate (between the startdates and
enddates in cols E and F)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
ps.com...
I have a task which seems relatively easy at first. I have two dates
as inputs: a start date and a stop date that can vary between dates
and lenght of time. I have a header row with dates by month. I am
trying to write a formula that will tell me how many days between the
start and stop date are in each month. For example, if I start on Jan
30 and end on Feb 2nd of the same year, I will show 2 under the Jan
header and 2 under the Feb header. Sometimes it will go on for a
couple months, so if it is a complete months, say starts on Jan 30th
and Ends on Mar 2, I will need it say 2 under Jan, 28 under Feb, and 2
under March. I have tried to Dateif, but not sure if I am looking at
it correctly? Any suggestions would be most helpful. Thank you in
advance!