cancel
Showing results for
Did you mean:
Highlighted
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

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

## 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!
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