Forum Discussion

ArchiveUser's avatar
ArchiveUser
Qrew Captain
8 years ago

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

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!
  • JohnThomas's avatar
    JohnThomas
    Qrew Cadet
    8 years ago
    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
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    There should be no need to nest the IFs like Excel does, but I don't see anything wrong with the original formula.
  • MichaelBarrow's avatar
    MichaelBarrow
    Qrew Cadet
    8 years ago
    What about null field value issues? How well would the comparison work if one or both fields were null?
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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's avatar
    ArchiveUser
    Qrew Captain
    8 years ago
    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!!!
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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's avatar
    ArchiveUser
    Qrew Captain
    8 years ago
    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's avatar
    AndrewLandry
    Qrew Trainee
    6 years ago
    I know it has been some time since this thread but I'm having a similar problem specifically with workdates. any ideas?
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      6 years ago
      You can convert work date field types to normal date fields within a different formula field in a formula using

      ToDate([My Work Date field])

      Then use normal date arithmetic in formuals.
    • AndrewLandry's avatar
      AndrewLandry
      Qrew Trainee
      6 years ago
      ah, I see. So I need to create 2 new fields to convert the work date fields? Wonder why QB doesnt allow operators on Work date fields. thanks for your help
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      6 years ago
      You can create two new fields but you don't have to.

      for example

      WeekdaySub(ToDate([my work date field 1]), ToDate([my work date field 2]))