Skip to main content
Question

Compare 2 dates and output a specific word in a third field

  • May 25, 2017
  • 0 replies
  • 179 views
I have a simple nested IF statement that works correctly in Excel, but in translating it to QB, it fails.

Excel Formula:  

=IF([Date Received] < $E$3, "GW", IF([Date Received] < $E$4, "INVERTER", "GOOD"))

The above formula looks at a date field and compares it against 2 fixed date fields and outputs GW or INVERTER if before the fixed date, otherwise it says GOOD.

In QB I just did a single if statement to test:

If([Date Received] < [Recloser Date], "GW", "GOOD")

This always defaults to GOOD no matter whether the [Date Received] is before or after the [Recloser Date].  The source fields are Date fields and the calculation field is a Formula-Text field due to the output being text.  But I did try a Formula-Date and QB threw an error.

Any help is appreciated on this seemingly easy issue.

Thanks!
    This topic has been closed for replies.

    • JohnThomas
    • Quickbase Alumni
    • May 25, 2017
    Use the below formula, Default value will be none and you will get both values based on conditions

    If([Date Received] < [Recloser Date], "GW", 
    If([Date Received] > [Recloser Date], "GOOD" ))

    I hope this will solve your problem
    There should be no need to nest the IFs like Excel does, but I don't see anything wrong with the original formula.
    • MichaelBarrow
    • Quickbase Alumni
    • May 27, 2017
    What about null field value issues? How well would the comparison work if one or both fields were null?
    I suggest that you specifically test for null with IsNull([date field]) to handle that situation and deal with specifying he result you want.

    IF(
    IsNull([Date Received],"say this",
    IsNull([Recloser Date], "say that",
    [Date Received] < [Recloser Date], "GW", 
    [Date Received] > [Recloser Date], "GOOD" )
    • ArchiveUser
    • Author
    • Quickbase Alumni
    • June 4, 2017
    I have tried these solutions, but I believe I am either missing a key concept, making the issue harder than it deserves to be, or just plain incompetent-I vote the last one.

    I created a table that was simply the dates and then used a relationship with a table where the result was to be used.  I just couldn't get it to pull in the data and work correctly.  I don't need to worry about the null option because I have to have the [Date Received] filled in.  

    I think I am just making this harder than it needs to be.

    Thanks to all for the help though!!!
    If you would like one on one assistance to get this working, you can contact me via the information on my web page at QuickBaseCoach.com
    • ArchiveUser
    • Author
    • Quickbase Alumni
    • June 28, 2017
    I have actually decided to try a different route.  I think the problem was in the original logic.  I am now going to build a formula in a dates table that is related to the table with the project date.  This will then return the value to the project table---hopefully.
    • AndrewLandry
    • Quickbase Alumni
    • July 3, 2019
    I know it has been some time since this thread but I'm having a similar problem specifically with workdates. any ideas?
    Terms & ConditionsAccessibility statement