Very special

[Guest]

Hi!!
I have two cells one with a date 06-01
and another with a date 06-15
the price is 1$ for a day this i can.

But in another sheet i have also 2 dates
06-10 and 07-20
the price for all days in this period is +1$

so my days in the first sheet are from 06-01 to 06-15
the days from 06-01 to 06-10 cost 1$
but the days from 06-10 to 06-15 cost 1$+1$

Is there anyone there can help me ??

Best regards alvin

 

[Tom Ogilvy]

=(Sheet1!A2-Sheet1!A1+1)*1 + (Sheet2!A2-Sheet2!A1+1)*1

So the overlap would be 1 + 1

 

[Guest]

Hi Tom
Well i try but hmmmmmmm.

I have this in sheet1
07-10 and 07-20 as date
in sheet2 i have

07-15 and 08-20 as date
when i use

=(sheet1!G1-sheet1!F1+1)*1 + (sheet2!H1-sheet2!G1+1)*1
then the result be 48 ???

What do i wrong??

Best regards alvin


"Tom Ogilvy" skrev:

 

[Tom Ogilvy]

48 is what I would expect. Perhaps I haven't understood the requirement.
What would you expect. If you want 41, then just do
=(Sheet2!H1-Sheet1!F1+1)*1

If the two sets of dates won't always overlap, then that would require more
complexity. Or if one set of dates could be totally within another, that
would also be important. We need to understand what the situation is and
what you want to count.

 

[Guest]

Ok

Well maybe the days wont always overlap
As you can see in my eks.
I have this in sheet1
07-10 and 07-20 as date
in sheet2 i have the period there cost more
07-15 and 08-20 as date

in this case i have a price from 07-10 to 07-15 at 1$
and from 07-15 to 07-20 at 2$

But again maybe the dates in sheet1 dont overlab sheet2
and maybe the holde period from sheet1 are in sheet2

hope you understand

Alvin




"Tom Ogilvy" skrev:

 

[Tom Ogilvy]

Possibly

=(Sheet1!G1-Sheet1!F1+1)*1-MAX(0,(Sheet1!G1-MIN(Sheet1!G1,Sheet2!G1)+1))*1 +
(Sheet2!H1-Sheet2!G1+1)*2