Discussions

Expand all | Collapse all

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

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

    Posted 02-26-2016 01:57

    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!



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

    Posted 02-26-2016 01:59
    no problem

    The formula for a formula date field would be

    IF(not IsNull([Date B]),[Date B],[Date A])


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

     
    Posted 03-27-2017 22:50
    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!


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

    Posted 03-28-2017 00:01
    • 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.


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

     
    Posted 03-28-2017 15:40
    D'oh! Thank you!


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

     
    Posted 04-05-2017 17:44
    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.


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

    Posted 04-05-2017 21:21
    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.


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

     
    Posted 04-05-2017 21:25
    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.


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

    Posted 04-05-2017 21:27
    OK.. IsNull does is not meant to work and does not work to test text fields for being blank.  So try my suggestion.


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

     
    Posted 04-05-2017 21:28
    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])


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

    Posted 04-05-2017 21:35
    is this what you want?
    If(
    [Related Customer (Referrals Only) - Lead - District] <>"", [Related Customer (Referrals Only) - Lead - District],
    [District])


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

     
    Posted 04-05-2017 22:38
    thank you so much. that was driving me crazy


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

     
    Posted 04-05-2017 22:38
    YOU ARE THE BEST! yes that is exactly it.


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

    Posted 04-05-2017 22:41
    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.