Sum between dates according to price table

November 20, 2009 at 03:40:44
Specs: Windows XP
Hello, my question is;
I have 2 columns where I write chech-in and check out dates. I also have two different prices as low/high seasons. I want to find out how many days customer stayed in low & season.

High Season dates are in between 01/04/2009 and 01/11/2009
Low season dates are in between 01/11/2009 and 01/04/2010

For example;

Check In Check Out H.Nights L.Nights
15/07/2009 18/07/2009 3 0
19/11/2009 27/02/2010 0 100
15/10/2009 25/11/2009 17 24

thank you very much in advance.

See More: Sum between dates according to price table

Report •

November 20, 2009 at 12:14:49

I have a solution for this issue. I have tested
it on the sample data you provided, plus one
extra to cover a transition from Low to High season,
but it has not been tested further, so
there may still be issues around Check In and
Check Out dates that land on the transition from
high to low season or vice versa.

To start with create a table of high/low seasons
in cells B2:C9, as follows:

	B		C
2	01-Apr-2008	High
3	01-Nov-2008	Low
4	01-Apr-2009	High
5	01-Nov-2009	Low
6	01-Apr-2010	High
7	01-Nov-2010	Low
8	01-Apr-2011	High
9	01-Nov-2011	Low

Note that the words High and Low must be exactly as
shown and in the column immediately to the right of
the dates.

Now enter sample check in and check out dates with
headings starting at cell E2, as follows:

	E		F		G	H
2	Check In	Check Out	Low	High
3	15-Jul-09	18-Jul-09	
4	19-Nov-09	27-Feb-10	
5	15-Oct-09	25-Nov-09	
6	10-Feb-09	03-Apr-09	

There are two formulas to be entered and then
they can be dragged down as many rows as
required, alongside check in/out dates.

In cell G3 enter:


In cell H3 enter:

Make sure you copy the complete formulas - they scroll way off the screen.
The $ signs in the formulas are essential, so
that the formulas can be dragged down and still
refer to the correct cells.
Drag the two formulas down to row 6.

Here is the output:

Check In	Check Out	Low	High
15-Jul-09	18-Jul-09	0	3
19-Nov-09	27-Feb-10	100	0
15-Oct-09	25-Nov-09	24	17
10-Feb-09	03-Apr-09	50	2

Once you have this in place and working, you
should be able to move the table of dates/season
to another location and the Check In/Out
table could also be moved.

Hope this is what you were looking for.


Report •
Related Solutions

Ask Question