What is the formula in calculating the years, months, and days of service in a given date?
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.
Answers
-
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.
Regards
J Tech
If my response has helped you in any way or provided a solution to your problem, please consider supporting the community by marking it as Insightful, Vote Up, or Awesome. Additionally, you can mark it as the accepted answer, which will make it easier for others to find a solution or get help with a similar issue in the future. Your support is greatly appreciated!
-
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?
-
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"
Regards
J Tech
If my response has helped you in any way or provided a solution to your problem, please consider supporting the community by marking it as Insightful, Vote Up, or Awesome. Additionally, you can mark it as the accepted answer, which will make it easier for others to find a solution or get help with a similar issue in the future. Your support is greatly appreciated!
-
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.
-
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", ...)
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
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.
-
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?
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
Thank you @Jason Albrecht, I tried that but it's calculating the entire days from the date given :(
-
@Mitch18 Try my solution here
https://community.smartsheet.com/discussion/comment/446982/
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!