calculate number of days

[Bazy2k]

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!

 

[Lars-Åke Aspelin]

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

 

[Bernard Liengme]

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

 

[barry houdini]

"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)))

 

[barry houdini]

Sorry, it needs a -1 too, i.e.

=MAX(0,MIN(C2,D$1)-MAX(B2-1,D$1-DAY(D$1)))

 

[Brad]

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

 

[Brad]

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))

 

[Bazy2k]

Thankyou everyone!

This solution was perfect, thanks!