Date Difference with IF Condition

[Anonymous]

I have the following Table:

I want to calculate the service yrs (today-adj_date or today - swd_date) based on the followinf condition:

if an employee has an ADJ_DATE, then service yrs should be calculated based on that ( today - adj_date) else if the employee does not have an adj_date then service years should be calculated as today - swd_date.

Is there any way to do this? Thank You!

[DoubleJ · Accepted solution]

You could add a calculated column with this formula

Service Years = 
IF(
    NOT ISBLANK(Employee[ADJ_Date]),
    (TODAY() - Employee[ADJ_Date])/365.25,
    (TODAY() - Employee[SWD_Date])/365.25)

One could argue that the division by 365.25 is not 100% accurate. But this is neglectable in this case I suppose.

Hope this helps

JJ

[v-lili6-msft · Accepted solution]

HI, @Anonymous 

You could use this formula as below:

Column = 
IF(
    ISBLANK('Table'[ADJ_DATE])=FALSE(),
    DATEDIFF('Table'[ADJ_DATE],TODAY(),DAY),
    DATEDIFF('Table'[SWD_DATE],TODAY(),DAY))

Result:

Best Regards,
Lin

[DoubleJ · Accepted solution]

You could add a calculated column with this formula

Service Years = 
IF(
    NOT ISBLANK(Employee[ADJ_Date]),
    (TODAY() - Employee[ADJ_Date])/365.25,
    (TODAY() - Employee[SWD_Date])/365.25)

One could argue that the division by 365.25 is not 100% accurate. But this is neglectable in this case I suppose.

Hope this helps

JJ