Forum Discussion
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!
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
3 Replies
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
Thank you SO much! I did have to change it slightly to look at the to.year = 2099 but the format worked!! CHAMP
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)
