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

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!

4 Replies

  • 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!