cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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
v-ljerr-msft Super Contributor
Super Contributor

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

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

 

Regards

4 REPLIES 4
Super User
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?






Did I answer your question? Mark my post as a solution.

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





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

 

v-ljerr-msft Super Contributor
Super Contributor

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

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

 

Regards

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

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

Kristoffer

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 262 members 2,974 guests
Please welcome our newest community members: