Forum Discussion
Nested IF and DATEDIFF formulas when some date fields are blank
NRW_admin1 You can use DATEDIFF when the dates are the same (see picture below) but you can't have blanks or negative values (to deal with negatives see link at the bottom)
Num of Days =
IF (
ISBLANK ( 'Table'[Made] ),
BLANK (),
DATEDIFF ( 'Table'[Received], 'Table'[Made], DAY )
)
Also see response here... (although this doesn't deal with Blank may give some ideas)
Problem solved, combined with guidance in the link. Many thanks :)
NRW_admin1
It is great to hear the problem got solved.It would be greatly appreciated of you if you can post the solution and accepting it as solution can help the people who would have the same problem to find the answer quickly. :)
To workaround the blank values in the date column, I created a new column with a false date (01/01/2025) for all the blanks:
false date (duly made) = IF(ISBLANK(permitapplicationSet[nrw_dulymadedate]), DATE(2025, 1, 1), permitapplicationSet[dulymadedate])
Then I used the formula to calculate number of days taken to duly make the applications:
Days to duly make = SWITCH(TRUE(), permitapplicationSet[datereceived]<permitapplicationSet[dulymadedate], DATEDIFF(permitapplicationSet[datereceived], permitapplicationSet[dulymadedate], DAY), permitapplicationSet[datereceived]>permitapplicationSet[dulymadedate], -1*DATEDIFF(permitapplicationSet[dulymadedate], permitapplicationSet[datereceived], DAY), 0)
This actually returned a blank for all rows with the false date. Which is great although I wasn't expecting that result!
