cancel
Showing results for
Did you mean:
Highlighted
Member

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

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..

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

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

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

4 REPLIES 4
Super User

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

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?

Proud to be a Datanaut! Appreciate your Kudos
Feel free to email me with any of your BI needs.

Member

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

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

Super Contributor

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

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

Member

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

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

Kristoffer

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 46 members 1,236 guests
Recent signins: