Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!See when key Fabric features will launch and what’s already live, all in one place and always up to date. Explore the new Fabric roadmap
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)
Solved! Go to 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!
WIPLayout = IF ( ISBLANK ( vRTT[DateOutOfPublishing] ), DATEDIFF ( vRTT[DateInPublishing], TODAY (), DAY ), DATEDIFF ( vRTT[DateInPublishing], vRTT[DateOutOfPublishing], DAY ) )
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?
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!
Thank you very much!
User | Count |
---|---|
87 | |
74 | |
69 | |
58 | |
55 |
User | Count |
---|---|
40 | |
38 | |
34 | |
32 | |
30 |