4 Replies Latest reply on Jan 30, 2016 10:50 AM by justinc

# Calculation which determines if current date falls in between two dates

Hello,

I am hoping to find some help with a calculation. I am trying to set up an Auto-enter field, based on a calculation. I have a database of Contracts, which all have a start date and end date. The agreement is 'active' if the start date is in the past and the end date is in the future. What I am trying to do with my calculation is to return an auto-enter for a field called 'Active', and it should return the text Yes or No.

So basically, if Start Date is in the past and the End Date is in the future, then the contract is Active, and should return Yes. I am trying to achieve this with an If-calculation, but I can't figure out how. I thought of something like this, but cannot get it to work:

If(StartDate  ≤ Get(CurrentDate) and EndDate  ≥  Get(CurrentDate))  ; "Yes" ; "No"

Any suggestions?

• ###### 1. Re: Calculation which determines if current date falls in between two dates

You closed it too fast.

If(StartDate  ≤ Get(CurrentDate) and EndDate  ≥  Get(CurrentDate)  ; "Yes" ; "No" )

• ###### 2. Re: Calculation which determines if current date falls in between two dates

Thanks for your help. Good to know I am somewhat on my way. However, it doesn't work quite yet. When I enter the Start Date, then the calculation returns "No". Whether the Start Date is in the past or future, it just remains "No" and the End Date does not have any influence on the result either. So no matter what dates I use, the result is always No. Apparently, the calculation is not correct. Anymore thoughts?

• ###### 3. Re: Calculation which determines if current date falls in between two dates

Never mind, I got it to work!

If((Start Date  ≤ Get(CurrentDate) and End Date  ≥  Get(CurrentDate))  ; "Yes" ; "No" )

I added one set of brackets and I had overlooked the replay existing value. Thanks for your help!

• ###### 4. Re: Calculation which determines if current date falls in between two dates

You shouldn't need that set of parenthesis around the logic clause.  The IF statement will evaluated clauses up to the semi-colon.  Not that they hurt anything.

I bet it was the 'replace existing...' checkbox that was the root of the problem.