Discussions

Expand all | Collapse all

Using the equals(=) operator when referencing a date to a number in an IF statement

  • 1.  Using the equals(=) operator when referencing a date to a number in an IF statement

    Posted 07-24-2018 13:40
    I am looking to create a numeric formula field in a table.  I am looking to define a specific number based on the date in a field called "Period Tested".  For example:

    If Period Tested = 01-01-2016, display the number "1"
    If Period Tested = 02-01-2016, display the number "4"
    etc.

    I've written it out as such:

    If([Period Tested]="01-01-2016",1,
    [Period Tested]="02-01-2016",4)

    I am using the Formula - Number type for the field.

    I get the following error when I try to save:

    The expression [Period Tested] on the left hand side of the operator "=" is of type date while the expression "01-01-2016" on the right hand side is of type text. The operator "=" cannot be used with these types of expressions.

    I'm not sure how to circumvent that in the system exactly, but I need the final number to show based on the date from the Period Tested field.  Any idea how I can work around this?  I looked a bit and didn't find any operator that could replace the equals(=) in this scenario.  

    Thanks for your time!


  • 2.  RE: Using the equals(=) operator when referencing a date to a number in an IF statement

    Silver
    Contributor
    Posted 07-24-2018 13:42

    try

    If(

    [Period Tested]=ToDate("01-01-2016"),1,
    [Period Tested]=ToDate("02-01-2016"),4

    )



  • 3.  RE: Using the equals(=) operator when referencing a date to a number in an IF statement

    Top
    Contributor
    Posted 07-24-2018 13:45
    If(
    [Period Tested] = Date(2016, 1, 1), 1,
    [Period Tested] = Date(2016, 2, 1), 4
    )

    Date(2000, 1, 10) returns the date January 10, 2000


  • 4.  RE: Using the equals(=) operator when referencing a date to a number in an IF statement

    Silver
    Contributor
    Posted 07-24-2018 13:58
    If [Period Tested] is a Date/Time field, you'll need to use the ToDate() function on [Period tested] too.


  • 5.  RE: Using the equals(=) operator when referencing a date to a number in an IF statement

    Posted 07-24-2018 16:57
    Thanks Chris and Dan, the ToDate() function worked fine in this scenario!