Date Range Formular

[Diverslife]

Hello all at ExcelBanter,

I have been looking around the Forums for a couple of days now and a
yet have been unable to find a solution to a calculation i would like t
make.

I have attached a copy of what i have so far. It's not much but i am ne
to Excel.
I have a set of Seasonal Dates in Column A and B with Nightly price
etc..

In I2 and J2 are Dates that can Input by the user.
What i would like to have is that column F shows up how many nights fal
between the season Dates based on the user Input Dates.

So in my example F6 should read as 16 and F7 as 2.
Hopefully that makes sense.
If anyone could give me a couple of hints or direct me to a Tutoria
that might help me i would be greatful

Many Thank

+-------------------------------------------------------------------
|Filename: Price calculation.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=473
+-------------------------------------------------------------------

 

[Vacuum Sealed]

Hi

Using your workbook as per your values:

F5 = 70 Nights
F6 = 16 Nights
F7 = 47 Nights.

I don't know where you got 2 from, either way, the formula that you
looking for is this, it will leave the cell blank if there is no date in
Column A.

In Cell F5 type =IF($A5="","",$B5-$A5)

Copy down as required

HTH
Mick.

 

[Vacuum Sealed]

Hello all at ExcelBanter,

I have been looking around the Forums for a couple of days now and as
yet have been unable to find a solution to a calculation i would like to
make.

I have attached a copy of what i have so far. It's not much but i am new
to Excel.
I have a set of Seasonal Dates in Column A and B with Nightly prices
etc..

In I2 and J2 are Dates that can Input by the user.
What i would like to have is that column F shows up how many nights fall
between the season Dates based on the user Input Dates.

Just thought I would have a second read through to make sure I got what
it is you were looking for and I noticed you already have the
subtraction formula as you stated in your 3rd paragraph.

With this in mind, if what I have posted is not what you want, then
please post back with more specific details, from where I'm at, you
already have the answer to your question in K2.

Cheers
Mick.

 

[Claus Busch]

Hi,

Am Wed, 18 Jul 2012 15:06:33 +0000 schrieb Diverslife:

In I2 and J2 are Dates that can Input by the user.
What i would like to have is that column F shows up how many nights fall
between the season Dates based on the user Input Dates.

in F5 try:
=IF(B5<$I$2,0,IF(AND(A5<$I$2,B5<$J$2),B5-$I$2,IF(AND(A5>$I$2,B5>$J$2),$J$2-A5,0)))
and drag down


Regards
Claus Busch

 

[Claus Busch]

Hi,

Am Thu, 19 Jul 2012 12:50:58 +0200 schrieb Claus Busch:

in F5 try:
=IF(B5<$I$2,0,IF(AND(A5<$I$2,B5<$J$2),B5-$I$2,IF(AND(A5>$I$2,B5>$J$2),$J$2-A5,0)))
and drag down

I forgot the special condition that all days fall into the season.
Try in F5:
=MAX((A5<$I$2)*(B5<$J$2)*(B5-$I$2),(A5>=$I$2)*(B5<=$J$2)*(B5-A5),(A5>$I$2)*(B5>$J$2)*($J$2-A5))


Regards
Claus Busch

 

[Ron Rosenfeld]

Hello all at ExcelBanter,

I have been looking around the Forums for a couple of days now and as
yet have been unable to find a solution to a calculation i would like to
make.

I have attached a copy of what i have so far. It's not much but i am new
to Excel.
I have a set of Seasonal Dates in Column A and B with Nightly prices
etc..

In I2 and J2 are Dates that can Input by the user.
What i would like to have is that column F shows up how many nights fall
between the season Dates based on the user Input Dates.

So in my example F6 should read as 16 and F7 as 2.
Hopefully that makes sense.
If anyone could give me a couple of hints or direct me to a Tutorial
that might help me i would be greatful

Many Thanks


+-------------------------------------------------------------------+
|Filename: Price calculation.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=473|
+-------------------------------------------------------------------+

It appears these are check in and check out days, and that you are counting the First Day, but are NOT counting the LAST day.
Given that, the following appears to work:

F5:
=MAX(0,SUMPRODUCT((ROW(INDIRECT(A5&":"&B5))>=$I$2)*
(ROW(INDIRECT(A5&":"&B5))<=$J$2))-($J$2<=B5))

and fill down as far as required.

If you do want to count the last day (in which case F7=3), then simplify the above to:

=SUMPRODUCT((ROW(INDIRECT(A5&":"&B5))>=$I$2)*
(ROW(INDIRECT(A5&":"&B5))<=$J$2))

This formula will fail in Excel 2007 or later after 25 November 4770.