Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! (This message was previously posted in microsoft.public.excel.links) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Specific Number of Days across Multiple Months
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)),"MM M")=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" wrote: 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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Specific Number of Days across Multiple Months
On Apr 13, 8:40 am, Ron Coderre
wrote: 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)),"MM M")=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" wrote: 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)- Hide quoted text - - 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.. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Specific Number of Days across Multiple Months
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 "cardan" wrote: On Apr 13, 8:40 am, Ron Coderre wrote: 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)),"MM M")=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" wrote: 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)- Hide quoted text - - 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.. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Specific Number of Days across Multiple Months
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) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Specific Number of Days across Multiple Months
On Apr 14, 5:29 am, "Max" wrote:
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),DAT E(*YEAR(L$2),MONTH(L$2),1)<DATE(YEAR($F3),MONTH($F 3),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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Specific Number of Days across Multiple Months
On Apr 13, 6:34 pm, Ron Coderre
wrote: 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 "cardan" wrote: On Apr 13, 8:40 am, Ron Coderre wrote: 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)),"MM M")=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" wrote: 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)- Hide quoted text - - 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..- Hide quoted text - - 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!! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Specific Number of Days across Multiple Months
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "cardan" wrote 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting no of days of a specific range of days from a list | Excel Discussion (Misc queries) | |||
Counting Days or Months | Excel Worksheet Functions | |||
how do i convert a number of days to years, months & days? | Excel Discussion (Misc queries) | |||
Counting Specific Number of Days across Multiple Months | Links and Linking in Excel | |||
how do I convert a number to number of years, months and days | Excel Worksheet Functions |