[[email protected]]

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!

[Max]

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!