Date query

C

Crimson King

Hi,

What is the simplest way to check 5 different dates to see if they lie
within 4 seperate date ranges(Yearly quarters) and count the number of days
attributable to each quarter?

eg
Q1 Jan 1 - Mar 31
Q2 Apr 1 - Jun 30
Q3 Jul 1 - Sep 30
Q4 Oct 1 - Dec 31

Voyage: 24 Mar, 25 Mar, 4 Apr, 5 Apr & 14 Apr

This voyage to place 7 days in Q1 (24 Mar to 31 Mar)
& 14 Days in Q2 (1 Apr to 14 Apr)

I need to check around 200 voyages at a time.

Is there an easy way?
 
B

Bob Phillips

=SUMPRODUCT(--(INT((MONTH(ROW(INDIRECT(A1&":"&B1)))+2)/3)=1))

=SUMPRODUCT(--(INT((MONTH(ROW(INDIRECT(A1&":"&B1)))+2)/3)=2))

etc.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
C

Crimson King

Bob,

Excellent! Your formula has solved 97% of my problem. Thank You.
The only issue I have remaining is how to ignore dates that are outside the
current year.

I was considering nesting an IF statement around your formula but am unsure
how best to do this. Any thoughts?



--
Many Thanks,
CK


Bob Phillips said:
=SUMPRODUCT(--(INT((MONTH(ROW(INDIRECT(A1&":"&B1)))+2)/3)=1))

=SUMPRODUCT(--(INT((MONTH(ROW(INDIRECT(A1&":"&B1)))+2)/3)=2))

etc.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
B

Bob Phillips

CK,

You need to add to the SP formula

=SUMPRODUCT(--(INT((MONTH(ROW(INDIRECT(A1&":"&B1)))+2)/3)=1),--(YEAR(ROW(INDIRECT(A1&":"&B1)))=YEAR(TODAY())))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



Crimson King said:
Bob,

Excellent! Your formula has solved 97% of my problem. Thank You.
The only issue I have remaining is how to ignore dates that are outside
the
current year.

I was considering nesting an IF statement around your formula but am
unsure
how best to do this. Any thoughts?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top