Forum Discussion

magnificentnile's avatar
magnificentnile
Regular Visitor
9 years ago
Solved

Date Diff Question using IF and ISBLANK

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 d...
Need Help
  • Sean's avatar
    Sean
    9 years ago

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

     

    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!

Sean's avatar
Sean
Community Champion
9 years ago
WIPLayout =
IF (
    ISBLANK ( vRTT[DateOutOfPublishing] ),
    DATEDIFF ( vRTT[DateInPublishing], TODAY (), DAY ),
    DATEDIFF ( vRTT[DateInPublishing], vRTT[DateOutOfPublishing], DAY )
)
magnificentnile's avatar
magnificentnile
Regular Visitor
9 years ago

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's avatar
    Sean
    Community Champion
    9 years ago

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

     

    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's avatar
      magnificentnile
      Regular Visitor
      9 years ago

      Thank you very much!