How to calculate days between two columns with blank cells in both columns

[Kristofferaabo]

Hi, I want to calculate the number of days between two columns. But some of the columns are blank leaving a very large number (e.g. 42356 or -34822)

There can be blank cells in both columns..

I have tried a nested IF OR ISBLANK and then a diffdate, but without luck..

[parry2k]

Try to make me understand how you like calculation to behave

Assuing you have two columns

Start Date

End Date

Calculate dates using above field, what you expect  if Start Date is blank or End Date is blank or both are blank?

[Kristofferaabo]

Hi @parry2k I have two columns A and B. I want to know how many days or months there is between A and B.

In both columns there are some blank cells leaving me with issues...

Thanks

Kristoffer

[v-ljerr-msft · Accepted solution]

Hi @Kristofferaabo,

---

In both columns there are some blank cells leaving me with issues...

---

So how do you want to show the value of how many days or months there is between A and B when there is blank in either of the two columns?

If you want to show the value as blank when there is blank in either of the two columns, then you should be able to use the formula below to create the calculate column to calculate how many days there is between column A and column B.

DaysDiff = 
IF (
    ( ISBLANK ( Table1[column A] ) ) || ( ISBLANK ( Table1[column B] ) ),
    BLANK (),
    DATEDIFF ( Table1[column A], Table1[column B], DAY )
)

If that is not the case, could you be more precisely with your requirements? It's better to post some sample data with your expected results, or share a sample pbix file in your scenario.

Regards

[Kristofferaabo]

Thanks, I was missing the couble ISBLANK! - this helped!

Kristoffer