Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
calculate number of days
Hi guys.
Ok i have items that run between two dates, each month i have to calculate how many days in that month relate to that item, eg. in Jan Item From To Jan Days a3 14/11/08 16/02/09 x a4 19/12/08 09/01/09 x So x = 31 x = 9 Is there a quick way to produce the number of jan days for each item between the two dates using a formula? Maybe linked to a date of 31/01/09? and then when i change the date to feb (28/02/09) the number of feb days are shown? I have a long list of items you see and want to make the process faster! Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
calculate number of days
On Tue, 30 Dec 2008 06:34:05 -0800, Bazy2k
wrote: Hi guys. Ok i have items that run between two dates, each month i have to calculate how many days in that month relate to that item, eg. in Jan Item From To Jan Days a3 14/11/08 16/02/09 x a4 19/12/08 09/01/09 x So x = 31 x = 9 Is there a quick way to produce the number of jan days for each item between the two dates using a formula? Maybe linked to a date of 31/01/09? and then when i change the date to feb (28/02/09) the number of feb days are shown? I have a long list of items you see and want to make the process faster! Thanks! If your From dates are in column B and your To dates are in column C and the dates start on row 2, try the follwing formula in cell D2: =MAX(0,MIN(C2+1,DATE(YEAR($D$1),MONTH($D$1)+1,1))-MAX(B2,DATE(YEAR($D$1),MONTH($D$1),1))) Copy down as far as you have dates in columns B and C: Cell D1 contains a date in the month you want the data for. Change $D$1to some other cell if that suits better. Hope this helps. / Lars-Åke |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
calculate number of days
With the first set of From and To dates in B2 and C2, respectively
=(MONTH(C2)=1)*DAY(C2)+(MONTH(C2)1)*31-(MONTH(B2)=1)*DAY(B2) With From 06/01/2009 and To as 12/02/2009, this gives 25 While 05/01/2009 and 09/01/2009 gives 4 You may need to add 1 to the formula to make an 'inclusive' count I have not allowed for more than 365 days between the two dates -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Bazy2k" wrote in message ... Hi guys. Ok i have items that run between two dates, each month i have to calculate how many days in that month relate to that item, eg. in Jan Item From To Jan Days a3 14/11/08 16/02/09 x a4 19/12/08 09/01/09 x So x = 31 x = 9 Is there a quick way to produce the number of jan days for each item between the two dates using a formula? Maybe linked to a date of 31/01/09? and then when i change the date to feb (28/02/09) the number of feb days are shown? I have a long list of items you see and want to make the process faster! Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
calculate number of days
"Maybe linked to a date of 31/01/09? and then when i change the date
to feb (28/02/09) the number of feb days are shown?" Given you have the last day of the month in question in D1 then you can adapt Lars' suggestion as follows: =MAX(0,MIN(C2,D$1)-MAX(B2,D$1-DAY(D$1))) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
calculate number of days
Sorry, it needs a -1 too, i.e.
=MAX(0,MIN(C2,D$1)-MAX(B2-1,D$1-DAY(D$1))) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
calculate number of days
Just a subtle change from what has been suggested
=MIN(c2,(DATE(YEAR(c2),1+1,0)))-MAX(b2,DATE(YEAR(b2)+(MONTH(b2)1),1,1))+1 Another way that could be used =Q22-P22-MAX(0,(Q22-DATE(2009,2,1)))-(DATE(2009,1,1)-P22) This assumes that all dates are within 12 months of each oother -- Wag more, bark less "Bazy2k" wrote: Hi guys. Ok i have items that run between two dates, each month i have to calculate how many days in that month relate to that item, eg. in Jan Item From To Jan Days a3 14/11/08 16/02/09 x a4 19/12/08 09/01/09 x So x = 31 x = 9 Is there a quick way to produce the number of jan days for each item between the two dates using a formula? Maybe linked to a date of 31/01/09? and then when i change the date to feb (28/02/09) the number of feb days are shown? I have a long list of items you see and want to make the process faster! Thanks! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
calculate number of days
One small change on the second method
=Q22-P22-MAX(0,(Q22-DATE(2009,2,1)))-(DATE(2009,1,1)-P22) sb =Q22-P22-MAX(0,(Q22-DATE(2009,2,1)))-max(0,(DATE(2009,1,1)-P22)) -- Wag more, bark less "Brad" wrote: Just a subtle change from what has been suggested =MIN(c2,(DATE(YEAR(c2),1+1,0)))-MAX(b2,DATE(YEAR(b2)+(MONTH(b2)1),1,1))+1 Another way that could be used =Q22-P22-MAX(0,(Q22-DATE(2009,2,1)))-(DATE(2009,1,1)-P22) This assumes that all dates are within 12 months of each oother -- Wag more, bark less "Bazy2k" wrote: Hi guys. Ok i have items that run between two dates, each month i have to calculate how many days in that month relate to that item, eg. in Jan Item From To Jan Days a3 14/11/08 16/02/09 x a4 19/12/08 09/01/09 x So x = 31 x = 9 Is there a quick way to produce the number of jan days for each item between the two dates using a formula? Maybe linked to a date of 31/01/09? and then when i change the date to feb (28/02/09) the number of feb days are shown? I have a long list of items you see and want to make the process faster! Thanks! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
calculate number of days
Thankyou everyone!
This solution was perfect, thanks! "barry houdini" wrote: "Maybe linked to a date of 31/01/09? and then when i change the date to feb (28/02/09) the number of feb days are shown?" Given you have the last day of the month in question in D1 then you can adapt Lars' suggestion as follows: =MAX(0,MIN(C2,D$1)-MAX(B2,D$1-DAY(D$1))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Within another function calculate and sum number of days | Excel Worksheet Functions | |||
Calculate Number of Days in a Month | Excel Worksheet Functions | |||
How do I calculate the number of business days? | Excel Discussion (Misc queries) | |||
calculate number of days btw dates | Excel Worksheet Functions | |||
calculate number of working days | Excel Worksheet Functions |