Forum Discussion
DateDiff with a BLANK value
RvdHeijden or you could say
test column =
IF ( startdate < enddate, DATEDIFF ( startdate, enddate, DAY ), BLANK () )
or
test column =
IF ( ISBLANK ( enddate ), BLANK (), DATEDIFF ( startdate, enddate, DAY ) )
if i understand your formula it doesn't return a value if the enddate is BLANK right ?
My question was that IF the enddate is BLANK it should use TODAY to fill in the blank Enddate
- test =
IF (
ISBLANK ( enddate ),
DATEDIFF ( startdate, TODAY (), DAY ),
DATEDIFF ( startdate, enddate, DAY )
)yep sorry missed that
That seems to work better but i just have 2 more questions
1. If the 'begindate' = 'enddate' it returns 0 but it should return 1, can i just +1 in the formula ?
2. i want it to calculate using workdays (sorry i didn't mention it the first time)
i have a 'Date' table and a colum 'IsWorkday' with value 0 and 1
RvdHeijden you could also do a switch ofr this could work
test =
IF (
IsWorkday = 1,
IF (
startdate = enddate,
1,
IF (
ISBLANK ( enddate ),
DATEDIFF ( startdate, TODAY (), DAY ),
DATEDIFF ( startdate, enddate, DAY )
)
),
BLANK ()
)