entry and exit dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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
 
Back
Top