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
no problem

The formula for a formula date field would be

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

Amber

  • 590 Points 500 badge 2x thumb
Hmm, I used this formula exactly and I'm getting the error: Formula error -- Bad or missing arguments in function call

  • If(not IsNull([Date]),[Date],[Date Modified])
My field type is a formula Date field. Should I be using another field type?

Thanks!
  • If(not IsNull([Date]),[Date],ToDate([Date Modified]))
The field [date modified] is actually a date/time field, not a date field.  So it needs to be converted to be a date type.
Photo of Amber

Amber

  • 590 Points 500 badge 2x thumb
D'oh! Thank you!
Photo of Ann Swelgin

Ann Swelgin

  • 382 Points 250 badge 2x thumb
How would I setup the formula if I want Date B unless Date B is blank then I want Date A. In my case it is a field called District.

If(not IsNull([Related Customer (Referrals Only) - Lead - District]),[Related Customer (Referrals Only) - Lead - District],[District])

I used this and it works great when t[Related Customer (Referrals Only) - Lead - District] is blank but I also want [Related Customer (Referrals Only) - Lead - District] to show up in my formula field when [Related Customer (Referrals Only) - Lead - District] is not blank.

Thank you for any help.
I'm confused by your question. What field type us the field you are testing.

If it is a text field then testing for IsNull will not work

You need to test for [ My Field]. = ""

Ie test if equal to empty quotes.
Photo of Ann Swelgin

Ann Swelgin

  • 382 Points 250 badge 2x thumb
District is a formula field on the Lead. and Related Customer (Referrals Only) - Lead - District is a lookup field to the district field of the Customer that referred the Lead.
OK.. IsNull does is not meant to work and does not work to test text fields for being blank.  So try my suggestion.
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])
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
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.