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?

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

• 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])

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...

• 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])
```