entry and exit dates

[Guest]

Hi: I have two issues somehow related.

1. I am trying to compute the number of days per quarter between an entry
and an exit date. The entry date can be anywhere between 1/1/2001 and
12/31/2005, and so does the exit date.

2. For the same entry and exit dates, I am trying to compute the total
number of days that fall in 2004 and the total that fall in 2005.

Thank you in advance.

 

[Guest]

Not quite sure what you're needing for part 1. Are you looking for number of
days? that's:
=Days360(start date,end date)

As far as the 2nd part, you should be able to do:

=min(end value,12/31/2004)-max(start value,1/1/2004) to get your 2004 number
of days. Format as number
=min(end value,12/31/2005)-max(start value,1/1/2005) to get your 2004 number
of days. Format as number

 

[T. Valko]

I'm assuming they're calendar quarters:

Jan - Mar = 1
Apr - Jun = 2
Jul - Sep = 3
Oct - Dec = 4

A2 = entry date
B2 = exit date

1Q: =SUMPRODUCT(--(MONTH(ROW(INDIRECT(A$2&":"&B$2)))={1,2,3}))
2Q: =SUMPRODUCT(--(MONTH(ROW(INDIRECT(A$2&":"&B$2)))={4,5,6}))
3Q: =SUMPRODUCT(--(MONTH(ROW(INDIRECT(A$2&":"&B$2)))={7,8,9}))
4Q: =SUMPRODUCT(--(MONTH(ROW(INDIRECT(A$2&":"&B$2)))={10,11,12}))

For the days in the years:

A10 = 2004
A11 = 2005

B10: =SUMPRODUCT(--(YEAR(ROW(INDIRECT(A$2&":"&B$2)))=A10))

Copy down to B11

Biff