# 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/2009Low season dates are in between 01/11/2009 and 01/04/2010 For example; Check In Check Out H.Nights L.Nights15/07/2009 18/07/2009 3 019/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

#1
November 20, 2009 at 12:14:49
 Hi,I have a solution for this issue. I have tested it on the sample data you provided, plus oneextra to cover a transition from Low to High season, but it has not been tested further, sothere may still be issues around Check In and Check Out dates that land on the transition fromhigh 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 thenthey can be dragged down as many rows asrequired, alongside check in/out dates.In cell G3 enter:`=IF(AND(VLOOKUP(\$E3,\$B\$2:\$C\$9,2)="Low",VLOOKUP(\$F3,\$B\$2:\$C\$9,2)="Low"),(\$F3-VLOOKUP(\$F3,\$B\$2:\$B\$9,1))-(\$E3-VLOOKUP(E3,\$B\$2:\$B\$9,1)),IF(AND(VLOOKUP(\$E3,\$B\$2:\$C\$9,2)="Low",VLOOKUP(\$F3,\$B\$2:\$C\$9,2)="High"),VLOOKUP(\$F3,\$B\$2:\$B\$9,1)-\$E3,IF(AND(VLOOKUP(\$E3,\$B\$2:\$C\$9,2)="High",VLOOKUP(\$F3,\$B\$2:\$C\$9,2)="Low"),\$F3-VLOOKUP(\$F3,\$B\$2:\$B\$9,1),0)))`In cell H3 enter:`=IF(AND(VLOOKUP(\$E3,\$B\$2:\$C\$9,2)="Low",VLOOKUP(\$F3,\$B\$2:\$C\$9,2)="Low"),0,IF(AND(VLOOKUP(\$E3,\$B\$2:\$C\$9,2)="Low",VLOOKUP(\$F3,\$B\$2:\$C\$9,2)="High"),\$F3-VLOOKUP(\$F3,\$B\$2:\$B\$9,1),IF(AND(VLOOKUP(\$E3,\$B\$2:\$C\$9,2)="High",VLOOKUP(\$F3,\$B\$2:\$C\$9,2)="Low"),VLOOKUP(\$F3,\$B\$2:\$B\$9,1)-\$E3,(\$F3-VLOOKUP(\$F3,\$B\$2:\$B\$9,1))-(\$E3-VLOOKUP(\$E3,\$B\$2:\$B\$9,1)))))`Note:Make sure you copy the complete formulas - they scroll way off the screen.The \$ signs in the formulas are essential, sothat the formulas can be dragged down and stillrefer 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, youshould be able to move the table of dates/seasonto another location and the Check In/Out table could also be moved.Hope this is what you were looking for.Regards

Report •
Related Solutions