Get the duration using a Calculated Field

[astroboy]

Can you help me with the formula? I have a field for hired date and end date and I want to display the duration that the employee is with the company. Here's the example result: 1 Year(s) 2 month(s) 26 day(s)

[Barbie]

Hi @astroboy,

If you're using a submission form and you want to save the result in the table you need to add a field where you will store the result. Set the form element of that specific field to Calculated Value and paste this formula:

CONVERT(VARCHAR, ABS(DATEDIFF( mm, [@field:StartDate], [@field:EndDate]) / 12)) + " Year(s) "
+
CONVERT(VARCHAR, ABS(DATEDIFF( mm, [@field:StartDate], [@field:EndDate]) % 12)) + " Month(s) "
+
CONVERT(VARCHAR,ABS(DATEDIFF( dd, DATEADD( mm, DATEDIFF( mm, [@field:StartDate], [@field:EndDate]), [@field:StartDate]), [@field:EndDate]))) + " Day(s)"

Make sure to change the field names. I hope this works.

Thanks,
Barbie

[ParkLoey]

Hi there! Just wanted to add that the DATEDIFF function has samples in Caspio's Function Reference: https://howto.caspio.com/function-reference/

They also have a list of how you can declare if you want to get the difference in different formats like, days, months, years, even hours and so on.