How do I create a formula field to display one of two date fields, depending on which one is not empty?

  • 1
  • 2
  • Question
  • Updated 2 years ago
  • Answered

I have two date fields. One is required [Date A], but the other is optional [Date B]. How do I create a formula field to default to display optional [Date B] unless [Date B] is empty, then display [Date A]? Thank you!

Photo of Jennifer

Jennifer

  • 20 Points

Posted 3 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,168 Points 50k badge 2x thumb
no problem

The formula for a formula date field would be

IF(not IsNull([Date B]),[Date B],[Date A])
Photo of Ann Swelgin

Ann Swelgin

  • 382 Points 250 badge 2x thumb
the is null part is working.

the second part I need is that I want the data in the Related Customer (Referrals Only) - Lead - District to be the "default". Essentially I want:

[Related Customer (Referrals Only) - Lead - District]
OR
If(not IsNull([Related Customer (Referrals Only) - Lead - District]),[Related Customer (Referrals Only) - Lead - District],[District])
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,168 Points 50k badge 2x thumb
is this what you want?
If(
[Related Customer (Referrals Only) - Lead - District] <>"", [Related Customer (Referrals Only) - Lead - District],
[District])
Photo of Ann Swelgin

Ann Swelgin

  • 382 Points 250 badge 2x thumb
YOU ARE THE BEST! yes that is exactly it.
Photo of Ann Swelgin

Ann Swelgin

  • 382 Points 250 badge 2x thumb
thank you so much. that was driving me crazy
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,168 Points 50k badge 2x thumb
The documentation actually says that it does not work on text fields, but since it works on pretty much every other field type, yes, that is a hidden trap waiting for you to fall into.