Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help need formula for working out date overlaps
Hope someone can help, I'm looking for a formula to work out date overlaps.
e.g. If my key dates are 1/11/08 to 30/11/08 I want to auto calculate how many days the following overlap with my key dates 01/01/08 to 31/12/08 16/11/08 to 05/12/08 etc thanks! -- Liz |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help need formula for working out date overlaps
=MAX(0,MIN(KeyDateEnd,$B1+1)-MAX(KeyDateStart,$A1))+1
-- __________________________________ HTH Bob "liztownsend" wrote in message ... Hope someone can help, I'm looking for a formula to work out date overlaps. e.g. If my key dates are 1/11/08 to 30/11/08 I want to auto calculate how many days the following overlap with my key dates 01/01/08 to 31/12/08 16/11/08 to 05/12/08 etc thanks! -- Liz |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help need formula for working out date overlaps
Try
1/1/2008 20/11/2008 9/11/2008 11/11/2008 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help need formula for working out date overlaps
Herbert Seidenberg wrote:
Try 1/1/2008 20/11/2008 9/11/2008 11/11/2008 Ah, a misplaced paren: =MAX(0,MIN(KeyDateEnd,$B1)+1-MAX(KeyDateStart,$A1))+1 ^^^ |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help need formula for working out date overlaps
smartin wrote:
Herbert Seidenberg wrote: Try 1/1/2008 20/11/2008 9/11/2008 11/11/2008 Ah, a misplaced paren: =MAX(0,MIN(KeyDateEnd,$B1)+1-MAX(KeyDateStart,$A1))+1 ^^^ If anyone is still looking at this, I wonder if there is an array solution that will do it? I think such a solution could have much wider applications that touch on set operations. E.g., In general, given two series of discreet values, denoted by their respective endpoints, series A: a...b and series B: c...d 1) Determine how many values are common to A and B, or 2) (better) Enumerate the values common to A and B so they can be counted, summed, etc. 3) (going a step further) Enumerate the non-intersect values of A and B Anyone up for a challenge? I know I am--and I dabbled at this, but my grasp of handling arrays is feeble at best. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help need formula for working out date overlaps
On Thu, 11 Sep 2008 03:27:01 -0700, liztownsend
wrote: Hope someone can help, I'm looking for a formula to work out date overlaps. e.g. If my key dates are 1/11/08 to 30/11/08 I want to auto calculate how many days the following overlap with my key dates 01/01/08 to 31/12/08 16/11/08 to 05/12/08 etc thanks! =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(A5&":"&B5)),ROW(INDIRE CT(KeyDateStart&":"&KeyDateEnd)),0))) Where your dates of interest are in A5 and B5 --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help need formula for working out date overlaps
The problem is that
1/11/08 to 30/11/08 when fully expanded reads: 1/11/08 12:00 AM to 30/11/08 12:00 AM What we really want is: 1/11/08 00:00 to 30/11/08 24:00 which shortens to: 1/11/08 to 1/12/08 If we correct all the end dates this way and remove all the +1's in Bob's formula, everything works. Ron's formula now fails and it has problems if we include odd times. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help need formula for working out date overlaps
I don't think so!
-- __________________________________ HTH Bob "smartin" wrote in message ... Herbert Seidenberg wrote: Try 1/1/2008 20/11/2008 9/11/2008 11/11/2008 Ah, a misplaced paren: =MAX(0,MIN(KeyDateEnd,$B1)+1-MAX(KeyDateStart,$A1))+1 ^^^ |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help need formula for working out date overlaps
Not all of them, just one I think
=MAX(0,MIN(KeyDateEnd,$B2)-MAX(KeyDateStart,$A2))+1 Is Herbert Seidenberg and liztownsend one and the same? -- __________________________________ HTH Bob "Herbert Seidenberg" wrote in message ... The problem is that 1/11/08 to 30/11/08 when fully expanded reads: 1/11/08 12:00 AM to 30/11/08 12:00 AM What we really want is: 1/11/08 00:00 to 30/11/08 24:00 which shortens to: 1/11/08 to 1/12/08 If we correct all the end dates this way and remove all the +1's in Bob's formula, everything works. Ron's formula now fails and it has problems if we include odd times. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help need formula for working out date overlaps
Ron's is an array formula, but why use an array formula when you can do it
without. -- __________________________________ HTH Bob "smartin" wrote in message ... smartin wrote: Herbert Seidenberg wrote: Try 1/1/2008 20/11/2008 9/11/2008 11/11/2008 Ah, a misplaced paren: =MAX(0,MIN(KeyDateEnd,$B1)+1-MAX(KeyDateStart,$A1))+1 ^^^ If anyone is still looking at this, I wonder if there is an array solution that will do it? I think such a solution could have much wider applications that touch on set operations. E.g., In general, given two series of discreet values, denoted by their respective endpoints, series A: a...b and series B: c...d 1) Determine how many values are common to A and B, or 2) (better) Enumerate the values common to A and B so they can be counted, summed, etc. 3) (going a step further) Enumerate the non-intersect values of A and B Anyone up for a challenge? I know I am--and I dabbled at this, but my grasp of handling arrays is feeble at best. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help need formula for working out date overlaps
On Fri, 12 Sep 2008 09:09:46 -0700 (PDT), Herbert Seidenberg
wrote: The problem is that 1/11/08 to 30/11/08 when fully expanded reads: 1/11/08 12:00 AM to 30/11/08 12:00 AM What we really want is: 1/11/08 00:00 to 30/11/08 24:00 which shortens to: 1/11/08 to 1/12/08 If we correct all the end dates this way and remove all the +1's in Bob's formula, everything works. Ron's formula now fails and it has problems if we include odd times. Herbert, My formula was not designed to handle anything other than full days, as requested. What are the circumstances under which it fails? --ron |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help need formula for working out date overlaps
On Fri, 12 Sep 2008 18:29:49 +0100, "Bob Phillips"
wrote: Ron's is an array formula, but why use an array formula when you can do it without. Easier for a simple mind like mine to understand? --ron |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help need formula for working out date overlaps
Bob Phillips wrote:
I don't think so! Heh! I must have missed something. Seemed like it worked, but I did not test using nonzero times in the dates. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help need formula for working out date overlaps
I think your mind is far from simple Ron
-- __________________________________ HTH Bob "Ron Rosenfeld" wrote in message ... On Fri, 12 Sep 2008 18:29:49 +0100, "Bob Phillips" wrote: Ron's is an array formula, but why use an array formula when you can do it without. Easier for a simple mind like mine to understand? --ron |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help need formula for working out date overlaps
On Sat, 13 Sep 2008 10:22:04 +0100, "Bob Phillips"
wrote: I think your mind is far from simple Ron -- Thank you. I guess what I'm really trying to say is that different people think in different ways. For me, in this kind of problem, it's easy for me to think in terms of arrays. And even if it is not the most efficient algorithm for solving the problem, unless the database is very large, it will likely be "good enough" for now. Your solution will certainly run faster, and will also not run into the dreaded "5 Jun 2079" problem in pre-2007 versions, as would my solution :-)) --ron |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help need formula for working out date overlaps
It was just a statement of fact by someone who reads your responses as a
matter of course because I know they will be enlightening. And my initial comment wasn't meant as criticism of your formula, but more as extra information for someone who joined in suggesting that an array formula might do it, but didn't actually offer one. As for 2079, I don't know about you, but I think it is beyond my realm of needing to worry about <bg -- __________________________________ HTH Bob "Ron Rosenfeld" wrote in message ... On Sat, 13 Sep 2008 10:22:04 +0100, "Bob Phillips" wrote: I think your mind is far from simple Ron -- Thank you. I guess what I'm really trying to say is that different people think in different ways. For me, in this kind of problem, it's easy for me to think in terms of arrays. And even if it is not the most efficient algorithm for solving the problem, unless the database is very large, it will likely be "good enough" for now. Your solution will certainly run faster, and will also not run into the dreaded "5 Jun 2079" problem in pre-2007 versions, as would my solution :-)) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculation Total Time With Dates/Times That Overlaps | Excel Discussion (Misc queries) | |||
Date in Formula not working | Excel Discussion (Misc queries) | |||
Plotting Bands for Task Overlaps | Charts and Charting in Excel | |||
Printing in Excel overlaps previous text | Excel Discussion (Misc queries) | |||
Identifying Date Overlaps | Excel Discussion (Misc queries) |