Forum Discussion

AUshYC's avatar
AUshYC
Qrew Trainee
13 days ago

Formula to test if a date falls in range compared to other dates - Expect Error

Hello,

I'm attempting to build a formula field that will mark a checkbox if [Date] is within 2 days of [Original Referral Date] OR [Date] is within 2 days of [Client - Re-opened Date] OR [Date] is within 2 days of [Client - Re-opened Date 2] OR [Date] is within 2 days of [Client - Re-opened Date 3] OR [Date] is within 2 days of [Client - Re-opened Date 4]. 

Essentially if the Date is within 2 Days of any of the above days I want to the checkbox to be marked! 

Here is the formula I've built to attempt this:

If(ToDate([Date]<=([Client - Original Referral Date]+Days(2)) or ToDate([Date]<=([Client - Original Referral Date]+Days(2)) or ToDate([Date]<=([Client - Re-Opened Date 2]+Days(2))or ToDate([Date]<=([Client - Re-Opened Date 3]+Days(2)) or ToDate([Date]<=([Client - Re-Opened Date 4]+Days(2)), true)

I keep getting an error on the last portion of this formula: "Expecting text/datetime/workdate/number but found bool" :(

Any ideas as to what I can do differently? This is my first time attempting to build a formula field and I appreciate any help or suggestions on this!

- Abby 

 

formulas
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    13 days ago

    My guess is that you have the field type as formula text.  If so, then you can change the field type to formula checkbox. It will preserve the formula when you make that change. 

    • AUshYC's avatar
      AUshYC
      Qrew Trainee
      11 days ago

      Hi Mark, thanks for the tip. I double checked and the field type is formula checkbox and the formula is still not working. It turned out the Date was already in date format and I did not need to include ToDate. That explains why it was expecting text!