days until expiration checkbox

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I have a checkbox that is functioning almost as its supposed too.

If(Today()>=([Expiration]-Days(45)), true and [Customer Status] = "Closed" and [Health Term] > 30)

I want the box to be checked if we are within 45 days of the policy expiration date and it does this. The issue is if the expiration date has passed the box doesn't unchecked.
Any assistance would be appreciated.
Thanks!
Photo of David Dziewit

David Dziewit

  • 220 Points 100 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb

Because Today() will continue to be greater than the expiration term. so you have to set a range.

Let's try this:

If(Today()>=([Expiration]-Days(45) AND Today()<=[Expiration]), true and [Customer Status] = "Closed" and [Health Term] > 30)

(Edited)
Photo of David Dziewit

David Dziewit

  • 220 Points 100 badge 2x thumb
That's giving me a syntax error.
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb

will you paste here what you have done?


Photo of David Dziewit

David Dziewit

  • 220 Points 100 badge 2x thumb
If(Today()>=([Expiration]-Days(45) AND Today()<=[Expiration]) and [Customer Status] = "Closed" and [Health Term] > 30,true,false)
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb

Can we try this without the parentheses around the date range like so:


If(
Today()>=[Expiration]-Days(45) AND Today()<=[Expiration] and [Customer Status] = "Closed" and [Health Term] > 30,true,false
)


If you get another syntax error, please tell us what is says.

Photo of David Dziewit

David Dziewit

  • 220 Points 100 badge 2x thumb
That appears to have worked.
Thank you so much!
Photo of Meredith Moore

Meredith Moore

  • 556 Points 500 badge 2x thumb
I am trying a similar thing, but not having any luck. 

If(
(not IsNull([Date Completed]) and [Status]="Completed"),[Date Completed],[Date Modified])


This is what it says:

The types of the arguments or the number of arguments supplied do not meet the requirements of the function If.

The function is defined to be If (Boolean condition1, result1, ..., else-result).

You may need to do one of the following to fix the problem:
  • Choose the right number and type of arguments.
  • Use a type conversion function to convert the arguments you are using to the correct type.
  • Choose a different function.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
I would check the field types of your two result conditions. I suspect that one of them is a date field and the other one might be a date/time field.

You need to decide what field type your formula is for and make sure that the results tmatch that.

You probably have to convert the date modified fieldto be a date field.

ToDate([Date Modified])