| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| calculating number of three month periods between two dates... | Excel Discussion (Misc queries) | |||
| function to fill all days of month to end of month | Excel Worksheet Functions | |||
| 4 and 5 week months | Excel Discussion (Misc queries) | |||
| Working days left in the month compared to previous months | Excel Worksheet Functions | |||
| formula to calculate # of days between dates, excluding holidays | Excel Discussion (Misc queries) | |||
Hybrid Mode
