• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Stuck in IF conditions

shaikhrulez

shaikhrulez

Active Member
Hello everyone,

Please refer to attached file. I would like to calculate amount from given table based on check-in and check-out dates entered in cell E6 and F6 respectively.

In column G, per nights rate are given for each range of dates.
 

Attachments

  • Chandoo_sample.xlsx
    11.1 KB · Views: 4
Hi ,

1. Will the stay period cover more than one period in the table ? For example , the check-in could be 14-06-2016 , and the check-out could be 10-07-2016.

2. Will the rates table be only these 3 entries or will it be a bigger table ?

Narayan
 
Thanks @NARAYANK991 for a prompt reply. That's only table and range.

This formula seems to be working somewhat okay for some cases, but when I entered the date range 26-06-2016 to 06-07-2016 it is giving an incorrect result.

I have mentioned the correct answer, beside your answer. Please have a look.

Thanks
 

Attachments

  • Image 1.png
    Image 1.png
    82.4 KB · Views: 4
  • Chandoo_sample (1).xlsx
    11.6 KB · Views: 5
Hi ,

I am assuming that the days are inclusive , thus if the check-in is on 26-06-2016 , and the check-out is also on the same day , the guest still has to pay for one day.

Thus between 26-06-2016 and 06-07-2016 , there would be 11 days and not 10.

That is the reason there is a +1 in the formula. If you think this is wrong , remove this +1 from the formula.

Narayan
 
You must log in or register to reply here.
Back
Top