Skip to Content
0

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

Apr 06, 2017 at 03:45 PM

59

avatar image
Former Member

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 left characters exceeded

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

0
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

0
* Please Login or Register to Answer, Follow or Comment.

2 Answers

AMIT KUMAR
Apr 06, 2017 at 06:00 PM
0

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

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

Show 3 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Okay... let me try!!!

0
Former Member

Okay, when I try that... I'm loosing the time - - it's just giving me the date MM/DD/YYYY... hmm...

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

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

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])
Share
10 |10000 characters needed characters left characters exceeded