• 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.

Missing IFS criteria

SbusyCR

New Member
Hi,

In regard to the attachment, I would like to know what is the missing formula part (see the end of the formula syntax) that will show me 2 as result in D6.

The nights' count in column D is based on the to/from in D1 and D2. It is about rooms occupancy from/to a range of past days.

Scenario is that C6 is empty because room guest has not checked out yet and today is Aug 16th

Cheers!
 

Attachments

Fluff13

Active Member
How about
=IF(B5="","",IF(C5="",$D$1-B5,IF(AND(B5<=$D$2,C5<=$D$1),C5-$D$2,IF(AND(B5<=$D$2,C5>$D$1),$D$1-$D$2,IF(AND(B5>$D$2,C5<=$D$1),C5-B5,IF(AND(B5>$D$2,C5>$D$1),$D$1-B5,"NA"))))))

I would advise against using the new style IFS formula as they can be resource intensive
 

SbusyCR

New Member
HI Fluff13,

The top-notch solution, you are awesome!

And yes, lesson learned with the resource-intensive comment.;)
 
Top