Skip to Content
avatar image
Former Member

Combining the RelativeDate() with an If Statement or Where Clause

These date functions is going to kill me - - so, here is my current dilemma...

I want to be able to combine the RelativeDate function with an If statement; as I don't want to effect all of the dates; just certain dates...

This is what I've done: =RelativeDate([Date1];-(1/24))... and this is working; but I want to be able to add an If statement; or a Where clause - - when I added the Where clause without an if statement; the format of the date changed to MM/DD/YYYY, and I returned a blank in the rows that are not included in the filter... but I want to keep my format...

Thought?

Add comment
10|10000 characters needed characters exceeded

  • can you share the example and what is required in IF-else condition?

  • Former Member

    I got it to work:

    I used this: =If([Attribute1] = "ABCD") Then FormatDate(RelativeDate([DATE1];-(1/24)); "M/dd/yyyy hh:mm:ss a") Else ([DATE1])

    - -

    Now, I am trying to figure out how to use my new variable within another variable....

    = ( ( (ToNumber(Substr(FormatDate([DATE2];"HH:mm:ss"); 1 ; 2 ) ) * 60 * 60 ) + (ToNumber(Substr(FormatDate([DATE2]; "HH:mm:ss"); 4 ; 2 ) ) * 60) + (ToNumber(Substr(FormatDate([DATE2]; "HH:mm:ss"); 7 ; 2 )))) - ((ToNumber(Substr(FormatDate([DATE1]; "HH:mm:ss"); 1 ; 2)) * 60 * 60 ) + (ToNumber(Substr(FormatDate([DATE1]; "HH:mm:ss"); 4 ; 2)) * 60) + (ToNumber(Substr(FormatDate([DATE1]; "HH:mm:ss"); 7 ; 2))) ) ) / 60 + (DaysBetween([DATE1];[DATE2])*1440)...

    I want to replace DATE1 with my variable - because DATE1 takes into account the (-1) hour; but I am throwing a "Wrong Data Type" error... kind of confused by that

  • Get RSS Feed

2 Answers

  • Apr 06, 2017 at 06:00 PM

    use this.After that below variable use in your time difference calculation.

    =If([Attribute1] = "ABCD") Then RelativeDate([DATE1];-(1/24)) Else ([DATE1])

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      The formula I came up with allows me to format, and subtract from the time... when I do the one you suggested, I am only able to view the date - - and I went to apply it to the other Time Variable, and the calculation is showing 00:00:00 because the days in most cases are the same...

  • avatar image
    Former Member
    Apr 06, 2017 at 07:12 PM

    For anyone who's interested, I figured it out - - ended up creating 2 variables, and then I was able to modify my existing variable - - Kumar, you helped me get there... so thank you!!!

    1. v1=RelativeDate([DATE1];-(1/24))
    2. v2=If([ATRIBUTE1]="ABCD") Then ([v1]) Else ([DATE1])
    
    Add comment
    10|10000 characters needed characters exceeded