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
Super User
Super User

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
Super User
Super User

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