| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date query
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? -- Many Thanks, CK |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date query
=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) "Crimson King" wrote in message ... 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? -- Many Thanks, CK |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date query
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" wrote: =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) "Crimson King" wrote in message ... 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? -- Many Thanks, CK |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date query
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" wrote in message ... 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" wrote: =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) "Crimson King" wrote in message ... 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? -- Many Thanks, CK |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date query
Bab,
This is exactly what I needed. Perfect! -- Many Thanks, CK "Bob Phillips" wrote: 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" wrote in message ... 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" wrote: =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) "Crimson King" wrote in message ... 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? -- Many Thanks, CK |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date Query | Excel Discussion (Misc queries) | |||
| Query Latest Date | Excel Discussion (Misc queries) | |||
| Date query? | Excel Discussion (Misc queries) | |||
| How do I query a future date? | Excel Worksheet Functions | |||
| How do I enter a date range ie -7 from current date in MS QUERY | Excel Discussion (Misc queries) | |||
Linear Mode
