What is the formula in calculating the years, months, and days of service in a given date?

Mitch18

Hi Everyone,

Can you pls help me figure out what is wrong with my formula? I wanted to get the exact, years, months, and days of service of our employees. I was able to do this in gsheet, but having a hard time here.

=YEAR(TODAY()) - YEAR([Date Hired]10) - IF(DATE(YEAR([Date Hired]10), MONTH(TODAY()), DAY(TODAY())) < [Date Hired]10, 1, 0) + " years " + IF(MONTH(TODAY()) - MONTH([Date Hired]10) < 0, 12 + MONTH(TODAY()) - MONTH([Date Hired]10), MONTH(TODAY()) - MONTH([Date Hired]10)) + " months " + IF(DAY(TODAY()) - DAY([Date Hired]10) < 0, 30 + DAY(TODAY()) - DAY([Date Hired]10), DAY(TODAY()) - DAY([Date Hired]10)) + " days"

Calculating inside the box is correct, but the rest seems to count backward. What do I need to change here? Appreciate your help.

J Tech

Hi @Mitch18

=YEAR(TODAY()) - YEAR([Date Hired]@row) - IF(DATE(YEAR([Date Hired]@row), MONTH(TODAY()), DAY(TODAY())) < [Date Hired]@row, 1, 0) + " years " + IF(MONTH(TODAY()) - MONTH([Date Hired]@row) < 0, 12 + MONTH(TODAY()) - MONTH([Date Hired]@row), MONTH(TODAY()) - MONTH([Date Hired]@row)) + " months " + IF(DAY(TODAY()) - DAY([Date Hired]@row) < 0, 30 + DAY(TODAY()) - DAY([Date Hired]@row), DAY(TODAY()) - DAY([Date Hired]@row)) + " days"

This should work to calculate the years, months, and days of service for each employee based on their individual hire date.

Mitch18

Hi @J Tech

Appreciate the help, however, it is the same formula that I am currently using. It is working, however, with regard to future dates the calculation is different. See the screenshot below, Feb 22, 2023, should only be just 28 days, and Feb 21, 2023, should be 29 days.

What is need to be changed or added to the formula to get the exact calculation for future dates too?

J Tech

Hi @Mitch18

Try =YEAR(TODAY()) - YEAR([Date Hired]@row) - IF(DATE(YEAR([Date Hired]@row), MONTH(TODAY()), DAY(TODAY())) < [Date Hired]@row, 1, 0) + " years " + IF(MONTH(TODAY()) - MONTH([Date Hired]@row) < 0, 12 + MONTH(TODAY()) - MONTH([Date Hired]@row), MONTH(TODAY()) - (MONTH([Date Hired]@row)) - 1) + " months " + IF(DAY(TODAY()) - DAY([Date Hired]@row) < 0, 30 + DAY(TODAY()) - DAY([Date Hired]@row), DAY(TODAY()) - DAY([Date Hired]@row)) + " days"

Mitch18

Hi @J Tech

I tried that however, it affects the months in the past dates. See the screenshot below.

Column A, is the formula and it's calculating ok but not in the future dates:

=YEAR(TODAY()) - YEAR([Date Hired]@row) - IF(DATE(YEAR([Date Hired]@row), MONTH(TODAY()), DAY(TODAY())) < [Date Hired]@row, 1, 0) + " years " + IF(MONTH(TODAY()) - MONTH([Date Hired]@row) < 0, 12 + MONTH(TODAY()) - MONTH([Date Hired]@row), MONTH(TODAY()) - MONTH([Date Hired]@row)) + " months " + IF(DAY(TODAY()) - DAY([Date Hired]@row) < 0, 30 + DAY(TODAY()) - DAY([Date Hired]@row), DAY(TODAY()) - DAY([Date Hired]@row)) + " days"

Column B is the formula you have given and which is not calculating the future and affected the past dates:

YEAR(TODAY()) - YEAR([Date Hired]@row) - IF(DATE(YEAR([Date Hired]@row), MONTH(TODAY()), DAY(TODAY())) < [Date Hired]@row, 1, 0) + " years " + IF(MONTH(TODAY()) - MONTH([Date Hired]@row) < 0, 12 + MONTH(TODAY()) - MONTH([Date Hired]@row), MONTH(TODAY()) - (MONTH([Date Hired]@row)) - 1) + " months " + IF(DAY(TODAY()) - DAY([Date Hired]@row) < 0, 30 + DAY(TODAY()) - DAY([Date Hired]@row), DAY(TODAY()) - DAY([Date Hired]@row)) + " days"

I don't know how to combine these 2 so we'll have the accurate length of service with the given hired date.

Jason Albrecht

Hi Mitch18,

Building on @J Tech insightful and helpful formula, and if I understand the question correctly, would it help to start the formula with something like 'if the column date is greater than todays date, display 0 years 0 months 0 days, otherwise follow the rest of the formula' i.e.,

IF([Date Hired]@row > TODAY(), "0 years, 0 months, 0 days", ...)

Mitch18

Hi Jason,

Appreciate your response, however, I am getting the same issue. I don't know what seems to be the problem in calculating future dates here in smartsheet, in gsheet it is calculating accurately. Kindly see the screenshot below.

here's the formula I used in smartsheet and I am getting this result.

=IF([Date Hired]@row > TODAY(), "0 years, 0 months, 0 days", YEAR(TODAY()) - YEAR([Date Hired]@row) - IF(DATE(YEAR([Date Hired]@row), MONTH(TODAY()), DAY(TODAY())) < [Date Hired]@row, 1, 0) + " years " + IF(MONTH(TODAY()) - MONTH([Date Hired]@row) < 0, 12 + MONTH(TODAY()) - MONTH([Date Hired]@row), MONTH(TODAY()) - MONTH([Date Hired]@row)) + " months " + IF(DAY(TODAY()) - DAY([Date Hired]@row) < 0, 30 + DAY(TODAY()) - DAY([Date Hired]@row), DAY(TODAY()) - DAY([Date Hired]@row)) + " days")

here's what I've got in gsheet,

First time using smartsheet and really don't know how to resolve this.

Jason Albrecht

Hi @Mitch18

May I suggest using the NETDAYS Function for calculating the number of days between the dates?

I suspect that 'adding 30' to the date will throw out the days, since not every month has 30 days.

it might look something like: ... + NETDAYS([Date Hired]@row, TODAY()) + " days")

Hope this helps?

Mitch18

Thank you @Jason Albrecht, I tried that but it's calculating the entire days from the date given :(

smdinhphan

@Mitch18 Try my solution here

https://community.smartsheet.com/discussion/comment/446982/