Formula to exclude year when using look up

[FinSue]

I am trying to create a bill for a local hotel where they have different prices depending on the time of year. For example between 15.4. and 15.8. are the summer prices and 16.8 -14.4 the winter prices

I have it set up so that when the room is selected it looks up the price from the corresponding list. However it is using the year also and will therefore only work for this years booking.

It is set to look up the corresponding rooms from different lists depending on time of year, the lists are as below.

Summer price 15.4. 15.8.
1hh 79,00 €
2hh 109,00 €
2hh+sauna 119,00 €
Apartment 5hh 230,00 €


Winter price 16.8. 14.4.
1hh 69,00 €
2hh 89,00 €
2hh+sauna 101,00 €
Apartment 5hh 210,00 €

The formula I am using at is,

=IFERROR(IF(AND(B11>=Sheet2!$B$1;B11<=Sheet2!$C$1);VLOOKUP(A11;Sheet2!$A$2:$B$6;2);VLOOKUP(A11;Sheet2!$A$9:$B$13;2));"")

It works unless I go over this year. Is there a way it can be set to ignore the year? or is it a case of changing the parameter year when it changes to 2018? or will it automatically change it, since I did not enter a year on the look up inputs but it automatically entered 2017 when the cell was formatted to a date, even in just day and month style?

[63falcondude]

Hello and welcome to the forum.

If you upload a sample workbook along with the desired results of a formula (manually entered), I'm sure we'll be able to help you with this.

[sandy666]

you can try this:
for the visual Summer/Winter: =IF(AND(NOW()>=DATE(YEAR(NOW()),4,15),NOW()<=DATE(YEAR(NOW()),8,16)),"Summer","Winter")
for the prices: =IF(AND(NOW()>=DATE(YEAR(NOW()),4,15),NOW()<=DATE(YEAR(NOW()),8,16)),$B$2:$B$5,$C$2:$C$5) - drag down

you can use TODAY() instead of NOW()

change system date to another year to see how it works

see attached file:

[Richard Buttrey]

As falcondude has suggested, upload the workbook so that we can understand exactly waht you're getting at. Be sure to manually add the results you want and clearly identify them.

However it would seem either build the year into the lookup table, i.e. extend the table, or leave the year out of the function value that's being looked up.

[FinSue]

This is the basics of it. I've highlighted and commented on the problem area.

[Pete_UK]

You can change the formula in C15 to this:

=IFERROR(IF(AND(DATE(2017,MONTH(B15),DAY(B15))>=Sheet2!$B$1,DATE(2017,MONTH(B15),DAY(B15))<=Sheet2!$C$1),VLOOKUP(A15,Sheet2!$A$2:$B$6,2),VLOOKUP(A15,Sheet2!$A$9:$B$13,2)),"")

so that it converts the date in B15 to the reference year (2017).

Hope this helps.

Pete

[FinSue]

Great! Thanks! It works :D

[Pete_UK]

You're welcome - thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

Pete