cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mo-PHAT Frequent Visitor
Frequent Visitor

DateDiff with IF statement

Hi,

 

I have 2 columns of dates and wish to get the days between these dates. Easy done with a datediff.

 

However sometimes the to date will be 31/12/2099 as these are dates in a job so that means they are currently in the job. So In this case I would want to use todays date.

 

I have tried the following:

 

DaysAtPos = DATEDIFF ([Date from].[Date] , IF ([Date to].[date] = 12/31/2099,NOW(),[Date to].[Date]), DAY)

 

This gets no errors but simply does not work, I end up with people being in positions for zillions of days which is clearly not right.

 

If I change the If statement to try and detect IF([Date to].[YEAR] = 2099 nd I get an error saying start dates can't be greater than end dates.

 

I also tried saying IF([Date to].[date] > NOW() then use NOW() and I get an error saying start dates can't be greater than end dates.

 

I have tried changing these date fields to all kinds of date formats and it's driving me nuts!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
CheenuSing Super Contributor
Super Contributor

Re: DateDiff with IF statement

Hi @Mo-PHAT

 

Try this

 

DaysAtPos = IF ([Date to].[date] = 12/31/2099, DATEDIFF ([Date from].[Date] , TODAY(), DAY) ,

                                                                            DATEDIFF([Date from].[Date],[Date to].[Date],DAY) )

 

If this works foryou please accept this as solution and also give KUDOS.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
2 REPLIES 2
Highlighted
CheenuSing Super Contributor
Super Contributor

Re: DateDiff with IF statement

Hi @Mo-PHAT

 

Try this

 

DaysAtPos = IF ([Date to].[date] = 12/31/2099, DATEDIFF ([Date from].[Date] , TODAY(), DAY) ,

                                                                            DATEDIFF([Date from].[Date],[Date to].[Date],DAY) )

 

If this works foryou please accept this as solution and also give KUDOS.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Mo-PHAT Frequent Visitor
Frequent Visitor

Re: DateDiff with IF statement

Thank you SO much!  I did have to change it slightly to look at the to.year = 2099 but the format worked!! CHAMP

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 300 members 3,263 guests
Please welcome our newest community members: