Forum Discussion
Nested IF and DATEDIFF formulas when some date fields are blank
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!