Counting Specific Number of Days across Multiple Months

[cardan]

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! (This message was previously posted in
microsoft.public.excel.links)

 

[Guest]

With this structure...
A1: (a start date)
B1: (an end date)

These cells contain text
C1: JAN
D1: FEB
etc
N1: DEC

Try something like this:
C2: =SUMPRODUCT(--(TEXT(ROW(INDEX($A:$A,$A$1):INDEX($A:$A,$B$1)),"MMM")=C1))

Copy C2 across through N2

Note: if you want to account for Montth/Year combinations, let us know.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[cardan]

With this structure...
A1: (a start date)
B1: (an end date)

These cells contain text
C1: JAN
D1: FEB
etc
N1: DEC

Try something like this:
C2: =SUMPRODUCT(--(TEXT(ROW(INDEX($A:$A,$A$1):INDEX($A:$A,$B$1)),"MMM")=C1))

Copy C2 across through N2

Note: if you want to account for Montth/Year combinations, let us know.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP





- Show quoted text -

Hi Ron, Thanks for the reply. I actually have the dates as EDATES so
my dates are by year and month so my two input dates are by month and
year. Is DATEIF the way to go? Thanks again for your help..

 

[Guest]

With
A1: (start date)
B1: (end date)
C1: (an EndOfMonth date eg 01/31/2007)
D1: (the next month's EOMonth date eg 02/28/2007)
etc

Then
This formula returns the number of days between the dates A1 and B1,
inclusive, that are in the month ending with the date in C1
C1: =MAX(MIN($B$1,C1)-MAX(C1-DAY(C1),$A$1-1),0)

Copy that formula across to the right

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Max]

Fwiw, the response below was posted to your multi-post in .links ..
(you should not multi-post)
--------
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)

---

 

[cardan]

Fwiw, the response below was posted to your multi-post in .links ..
(you should not multi-post)
--------
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)-$E­3+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),MO­NTH(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
Singaporehttp://savefile.com/projects/236895
xdemechanik
---

Thanks for the advice. I downloaded the spreadsheet that you linked
up. Thank you for that. It always helps when you can see the formulas
at work. Do you have to format the dates as "mmm-yy"" to work
properly? or can I put it my own date formatting (ie 4/23/2007) or
January 3, 2008? Thanks again.

 

[cardan]

With
A1: (start date)
B1: (end date)
C1: (an EndOfMonth date eg 01/31/2007)
D1: (the next month's EOMonth date eg 02/28/2007)
etc

Then
This formula returns the number of days between the dates A1 and B1,
inclusive, that are in the month ending with the date in C1
C1: =MAX(MIN($B$1,C1)-MAX(C1-DAY(C1),$A$1-1),0)

Copy that formula across to the right

Does that help?
***********
Regards,
Ron

XL2002, WinXP






- Show quoted text -

Your formula works great! My header rows are formatted as the first of
the months so I went ahead and modified your formula to read the dates
as the end of the months. Thanks again!!

 

[Max]

Do you have to format the dates as "mmm-yy"" to work properly?
It's not so much the formatting* as the assumptions made on the "monthly"
col set up in L2 across, and the results expected under each "monthly" col.
*Formatting doesn't change underlying values

---
Thanks for the advice. I downloaded the spreadsheet that you linked
up. Thank you for that. It always helps when you can see the formulas
at work. Do you have to format the dates as "mmm-yy"" to work
properly? or can I put it my own date formatting (ie 4/23/2007) or
January 3, 2008? Thanks again.