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

  • 2
  • 1
  • Question
  • Updated 1 year ago
  • Answered
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!
Photo of Neil Shepard

Neil Shepard

  • 1,248 Points 1k badge 2x thumb
  • Frustrated-not at QB but with computers in general

Posted 1 year ago

  • 2
  • 1
Photo of John Thomas

John Thomas

  • 1,062 Points 1k badge 2x thumb
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
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,326 Points 50k badge 2x thumb
There should be no need to nest the IFs like Excel does, but I don't see anything wrong with the original formula.
Photo of Michael Barrow

Michael Barrow

  • 2,206 Points 2k badge 2x thumb
What about null field value issues? How well would the comparison work if one or both fields were null?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,326 Points 50k badge 2x thumb
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" )
Photo of Neil Shepard

Neil Shepard

  • 1,248 Points 1k badge 2x thumb
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!!!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,326 Points 50k badge 2x thumb
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
Photo of Neil Shepard

Neil Shepard

  • 1,248 Points 1k badge 2x thumb
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.