DateDiff with IF statement

[Mo-PHAT]

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!

[Cockatoo01]

I'm writing this and getting a scalar error and wondering if there is another way to do this. Please advise, thanks!

Average Day =

IF(tablename, fieldname = "Contractor"),datediff('Out-Processing'[Departure Date], 'Out-Processing'[OPS Chief Signature_], day)

[CheenuSing · Accepted solution]

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

[Mo-PHAT]

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