how to get difference between two dates in sharepoint calculated column

1
votes

how to get different between two dates: column1 "joining date" column2 "leaving date", in sharepoint calculated column as "total experience of an employee"?

The desired output I'm searching is in this format for example: "2 days 2 months 2 years".

1
sharepointsharepoint-2013
noted the mistakes @James Z, Will follow that up. - Ohana

1 Answers

0
votes

this formula should give you the answers that you are after

= DATEDIF([Date 1],[Date 2],"MD")&" Days "& DATEDIF([Date 1],[Date 2],"YM")  &" Months "& DATEDIF([Date 1],[Date 2],"Y") &" Years"

Steps:

  1. Create a calculated column of type Single Line of text
  2. Use the formula above.

Explanation:

Formula goes as

DATEDIF(Start_date,End_date,"Interval")

Where interval can be:

  • Y Calculate the number of complete years
  • M Calculate the number of complete months
  • D Calculate the number of days
  • YM Calculate the number of months excluding years
  • MD Calculate the number of days excluding years and months
  • YD Calculate the number of days excluding years

For 2 dates like Date 1 (31/01/2020) and Date 2 (14/05/2020), the results will be:

14 Days 3 Months 0 Years