Date Diff Question using IF and ISBLANK

[magnificentnile]

I have 2 columns

one vRTT[DateInPublishing 

vRTT[DateOutOfPublishing]

If vRTT[DateOutOfPublishing] ISBLANK I would like DateDiff to calculate based on TODAY() otherwise it would calculate the datediff between the In and Out fields. 

I thought something like the following woud work...

WIPLayout = DATEDIFF(vRTT[DateInPublishing],
(IF(ISBLANK(vRTT[DateOutOfPublishing]), TODAY(), vRTT[DateOutOfPublishing])),
vRTT[DateOutOfPublishing],DAY)

[Sean]

WIPLayout =
IF (
    ISBLANK ( vRTT[DateOutOfPublishing] ),
    DATEDIFF ( vRTT[DateInPublishing], TODAY (), DAY ),
    DATEDIFF ( vRTT[DateInPublishing], vRTT[DateOutOfPublishing], DAY )
)

[magnificentnile]

Supplental question:

WIPapproval = DATEDIFF(vRTT[DateOutOfPublishing], (IF(ISBLANK(vRTT[PublishedDate]), TODAY(), vRTT[PublishedDate])), DAY) 

returns an error:

In DATEDIFF function, the start date cannot be greater than the end date

How would I put an IF statement in front to capture the situations where the DATEDIFF throws the error?

[Sean · Accepted solution]

Okay I think we got this - test it to make sure

WIPLayout =
IF (
    ISBLANK ( vRTT[DateOutOfPublishing] ),
    SWITCH (
        TRUE (),
        TODAY () > vRTT[DateInPublishing], DATEDIFF ( vRTT[DateInPublishing], TODAY (), DAY ),
        TODAY () < vRTT[DateInPublishing], DATEDIFF ( TODAY (), vRTT[DateInPublishing], DAY ) * -1,
        0
    ),
    SWITCH (
        TRUE (),
        vRTT[DateOutOfPublishing] > vRTT[DateInPublishing], DATEDIFF ( vRTT[DateInPublishing], vRTT[DateOutOfPublishing], DAY ),
        vRTT[DateOutOfPublishing] < vRTT[DateInPublishing], DATEDIFF ( vRTT[DateOutOfPublishing], vRTT[DateInPublishing], DAY ) * -1,
        0
    )
)

EDIT:

So if the "OutOf" or "published" date is blank

The first SWITCH checks whether the "In" or "submitted" date is so to speak in the future i.e. > today

this is what throws off the error (those results will be negative * -1)

The second SWITCH checks whether it could have been "published" before it was ever "submitted"

again those results will be negative

Hope this makes sense!

[magnificentnile]

Thank you very much!