Does a date fall ON or Between two dates?

Question

So I been fighting with this code for a few days, scouring the page and google for some answers and I thought I had it nailed. Today I found out I was wrong about ONE date...and I can't see to solve it. I'll try to explain what I'm doing then post some snippets and see if we can put our collective heads together and solve this one.

I work for a Major Hospitality Brand, and our owners have certain Booking Windows for hotels. Platinum Members can book between 45-days and 10 months, and non-platinum can only book 2 months to 10 months (in this case just months, not 60 days). So I have a date calculator that calculates the different time frames based on TODAY()

We also have a spot where you can put in a date the owner is requesting, which evaluates the booking window for the owner

Ultimately the goal is to have a section on the side that will show my users IF the date entered in Date Calculator is eligible for both owner types to book or not.

The formula I am using is this:

Non-Platinum: =IF(AND(F3>$F$19,F3<$F$20),"OK to Book","Not Ok to Book")

Platinum: =IF(AND(F3>=$F$19,F3<=$F$21),"Ok to Book","Not Ok to Book")

If I enter a date that is clearly outside the range, it works fine. The problem I'm having is when I'm trying to compare a date that falls ON one of the extremes

UPDATE: Here is a link to an excel sheet that I made with the data I'm working on DateCalculator

Supposed that might help...lol F3 = Today F19 = 10 Months from the Requested Date (2nd image) F20 = 2 Months from Requested Date F21 = 45 Days from Requested Date — C.Croel
There's probably an issue with your comparison, but your screenshot of data is virtually useless for doing proper troubleshooting. It cannot be copy/pasted into a worksheet. One can try an OCR program, or manually enter it. Having to do either of these is discouraging to those who might assist you. To make the data useful edit your question to post it as text, perhaps using this Markdown Tables Generator, or possibly upload a workbook (with sensitive information removed) to some public website and post a link in your original question — Ron Rosenfeld
one note is you are using >= for platinum equation, but only > for non-platinum — Forward Ed
Lemme pull the calculator and formulas out into a seperate excel and upload that to play with!! thanks for the tip @RonRosenfeld — C.Croel

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2018-08-08T19:48:38

Based on your uploaded workbook, and considering that the tables might be in different positions on your real workbook, I made some changes:

B2: =TODAY()
- =NOW() which you had in there, includes the time
Your references to cells and your equality operators both seem incorrect, even taking into account what you wrote about the contents of column f in you comment above.
To clarify things, I created named references:

_10_MONTHS     refers to:   =Sheet1!$B$6
_2_MONTHS      refers to:   =Sheet1!$B$5
_45_DAYS       refers to:   =Sheet1!$B$4
REQUESTED_DATE refers to:   =Sheet1!$B$13

Then use these formula, adjusting the equality operator as you wish:

NON-PLAT:  =IF(AND(REQUESTED_DATE>=_2_MONTHS,REQUESTED_DATE<=_10_MONTHS),"OK to Book","Not Ok to Book")
PLAT:     =IF(AND(REQUESTED_DATE>=_45_DAYS,REQUESTED_DATE<=_10_MONTHS),"OK to Book","Not Ok to Book")

Oh, I also changed your calculation formulas to use simple addition and/or edate, depending on the results.

Here is the result:

This should get you started as to how to approach your "real" workbook.

Does a date fall ON or Between two dates?

1 Answers