Discussions

 View Only
Expand all | Collapse all

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

  • 1.  Compare 2 dates and output a specific word in a third field

    Posted 05-25-2017 16:50
    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!


  • 2.  RE: Compare 2 dates and output a specific word in a third field

    Posted 05-25-2017 18:02
    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


  • 3.  RE: Compare 2 dates and output a specific word in a third field

    Posted 05-25-2017 18:07
    There should be no need to nest the IFs like Excel does, but I don't see anything wrong with the original formula.


  • 4.  RE: Compare 2 dates and output a specific word in a third field

    Posted 05-26-2017 20:23
    What about null field value issues? How well would the comparison work if one or both fields were null?


  • 5.  RE: Compare 2 dates and output a specific word in a third field

    Posted 05-26-2017 20:30
    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" )


  • 6.  RE: Compare 2 dates and output a specific word in a third field

    Posted 06-04-2017 02:05
    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!!!


  • 7.  RE: Compare 2 dates and output a specific word in a third field

    Posted 06-04-2017 02:12
    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


  • 8.  RE: Compare 2 dates and output a specific word in a third field

    Posted 06-27-2017 20:50
    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.


  • 9.  RE: Compare 2 dates and output a specific word in a third field

    Posted 07-03-2019 02:12
    I know it has been some time since this thread but I'm having a similar problem specifically with workdates. any ideas?


  • 10.  RE: Compare 2 dates and output a specific word in a third field

    Posted 07-03-2019 02:18
    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.


  • 11.  RE: Compare 2 dates and output a specific word in a third field

    Posted 07-03-2019 02:20
    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


  • 12.  RE: Compare 2 dates and output a specific word in a third field

    Posted 07-03-2019 02:23
    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]))


  • 13.  RE: Compare 2 dates and output a specific word in a third field

    Posted 07-03-2019 02:30
    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


  • 14.  RE: Compare 2 dates and output a specific word in a third field

    Posted 07-03-2019 02:35
    No, but try this

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


  • 15.  RE: Compare 2 dates and output a specific word in a third field

    Posted 07-03-2019 02:38
    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?


  • 16.  RE: Compare 2 dates and output a specific word in a third field

    Posted 07-03-2019 02:51
    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.



  • 17.  RE: Compare 2 dates and output a specific word in a third field

    Posted 07-03-2019 02:53
    thank you so much, Mark!