FIND amount of days that appear in two periods

[tarynmahon]

I have a period of 01/02/13 - 30/03/13


I have another period of 01/02/13 - 05/02/13


I need to know how many days of the first period (01/02/13-30/03/13) appear in the second period (01/02/13-05/02/13)

Answer = 5

Please help, this is driving me mad!

 

[Luke M]

=ROUNDUP(MAX(0,MIN(H2,H3)-MAX(G2,G3))*1.000001,0)


This first 0 is to make sure you never get a negative value. THe MIN/MAX combos find the biggest smallest overlap. The *1.00001 at end is needed because you are doing an inclusive count (e.g., 5-1 = 4, but there are 5 numbers in the range {1,5}). This multiplication causes any number other than 0 to be pushed up a tiny bit, which then gets rounded to next whole number.

 

[oldchippy]

Hi,


This seems to work, unless I'm missing something?


=MIN(H3,H2)-MAX(G3,G2)+1


Where start dates in column G end dates in column H

 

[tarynmahon]

Unfortunately oldchippy that produces negative numbers,

Luke, that formula does not give me a 1 where the start date of the first period is the same as the end date of the second period, it returns a 0 ??

 

[Debraj]

Hi Tarynmahon,


he he .. me again..


https://dl.dropbox.com/u/78831150/Excel/Find%20how%20many%20weeks%20in%20a%20period.xlsx

{=SUM(IFERROR(IF(ROW(INDIRECT(FprStart&":"&FPrEnd))=ROW(INDIRECT(SPrStart&":"&SPREnd)),1),""))}

I think its somehow similar with last post..


Regards,

Deb

 

[Hui]

You could also use the Google Search Box at the Top right of this and every Chandoo.org screen to search for "two time periods overlap"


http://chandoo.org/wp/2010/06/01/date-overlap-formulas/

 

[srinidhi]

First please set you date to a single format the first date 01/02/13 is dd/mm/YY format & 30/03/13 is in dd/mm/yy format.


Assuming:

A1 has 01/02/13 B1 03/30/13


A2 has 01/02/13 B2 02/05/13


Next Apply this formula


=IF(a2>=a1,IF(b2=b1,(b2-a2)))

 

[Luke M]

Ah, forgot about a single date overlap. Retry:

=MAX(0,MIN(B2,B3)+1-MAX(A2,A3))

 

[tarynmahon]

Right!

Debraj, for some reason that formula doesnt work on my workbook, I just get 0 for all lines

Hui, Unfortunately this doesnt tell me the amount of days that fall in the period overlap

Srinidhi, First of all, in the period range specified its bringing back 4 when it should be 5 and for other ranges it just says FALSE

Luke, Im relying on yours as you've always seemed to crack it for me but just a bit unsure what your B2, B3, A2, A3 reference relate to please?

 

[tarynmahon]

Scrap that Luke, worked it out, now to test!

 

[tarynmahon]

Success! Luke, I think I love you!

 

[Hui]

Sorry about that

There were two related and similar looking posts

I should have checked before posting


Anyway here is the post

http://chandoo.org/wp/2010/07/07/days-overlapped-excel-formula/

 

[Luke M]

Glad to know that Cyril Z and I came up with same idea. =)