Forum Discussion
DateDiff with a BLANK value
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
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 ()
)That wont work i think, the 'IsWorkday' is in the table 'Date' en the rest is in the table 'Klachten'
i have a relationship between both tables on 'Date' ofcourse but i think your formula needs to be changed