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

  • 2
  • 1
  • Question
  • Updated 2 weeks 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 2 years ago

  • 2
  • 1
Photo of John Thomas

John Thomas

  • 1,082 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
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,216 Points 2k badge 2x thumb
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" )
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!!!
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.
Photo of Andrew Landry

Andrew Landry

  • 180 Points 100 badge 2x thumb
I know it has been some time since this thread but I'm having a similar problem specifically with workdates. any ideas?
Photo of Andrew Landry

Andrew Landry

  • 180 Points 100 badge 2x thumb
so comparing the dates would look something like this?

WeekdaySub(ToDate([Actual Finish]) <= ToDate([Projected Finish])), On time
WeekdaySub(ToDate([Actual Finish]) > ToDate([Projected Finish])), Late
No, but try this

IF(
ToDate([Actual Finish]) <= ToDate([Projected Finish]), "On time",
ToDate([Actual Finish]) > ToDate([Projected Finish]), "Late")
Photo of Andrew Landry

Andrew Landry

  • 180 Points 100 badge 2x thumb
Wow, that worked. I can't thank you enough. Any chance I could add a color to the field? green for on time and red for late?
Low Tech is to create a new formula Rich text field

IF(
ToDate([Actual Finish]) <= ToDate([Projected Finish]), "<font color=green>On time",

ToDate([Actual Finish]) > ToDate([Projected Finish]), "<font color=red>"Late")


or else
IF(
ToDate([Actual Finish]) <= ToDate([Projected Finish]), "<b><font color=green>On time",

ToDate([Actual Finish]) > ToDate([Projected Finish]), "<b><font color=red>"Late")



If you want fancier background shading, then you will need to use this help here

https://help.quickbase.com/user-assistance/color_field.html


or perhaps cheat off an app in the Exchange called Magic Buttons.

Photo of Andrew Landry

Andrew Landry

  • 180 Points 100 badge 2x thumb
thank you so much, Mark!