How to create a formula that auto-populates a field (with text) based on two separate fields/dates, which are inputs, and today's date, which is not an input?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

For example, if the "Start Date" field is input as January 1st and the "End Date" field is input as January 31st, and today's date is Jan 15th, I want to automatically populate the text "Active" in a new field. If it's not between Jan 1 and Jan 31, I want the field to say "Expired/Inactive".

Photo of David

David

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,316 Points 50k badge 2x thumb
IF(Today()>=[Start Date] and Today()<=[End Date],"Active", "Expired/Inactive")
Photo of David

David

  • 0 Points
Awesome!  Thanks Mark.  Additional question - is there a way to augment the formula so the auto-populated field is left blank if either the start or end date is not filled in?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,316 Points 50k badge 2x thumb
no problem,

IF(

IsNull([Start Date]) or IsNull([End Date]),"",


Today()>=[Start Date] and Today()<=[End Date],"Active", "Expired/Inactive")
Photo of David

David

  • 0 Points
Perfect!