FORMULA FROM DATE TO DATE
Good day all,
My questions is, I need to know from my list the age (years & Months) of the person at the time I received the approval of their requets. I have it in excel =DATEDIF(D3;F3;"Y") . Can someone help me make this formula for my spreadsheet?
Regards,
Laura G.
Best Answer
-
Your Excel formula only gives years, not years and months. Here's a few options.
If Date1 is their birthday and Date2 is their request date, then you can get years by this:
=ROUNDDOWN(NETDAYS([Date1]@row, [Date2]@row) / 365, 0)
RESULT: 62
-------------------------------
If you want decimal years, use it without the rounding (you may be a few days off due to leap years):
=NETDAYS([Date1]@row, [Date2]@row) / 365
RESULT: 62.93
------------------------------
If you want years and months, it's more complicated, but leap years won't have an impact (the result is "YEARS / MONTHS"):
=IF(MONTH([Date2]@row) - MONTH([Date1]@row) < 0, ((YEAR([Date2]@row) - YEAR([Date1]@row) - 1) + " / " + (MONTH([Date2]@row) - MONTH([Date1]@row) + 12)), (YEAR([Date2]@row) - YEAR([Date1]@row)) + " / " + (MONTH([Date2]@row) - MONTH([Date1]@row)))
RESULT: 62 / 11
-------------------------------
If you don't mind 2 helper columns plus the result column, you can achieve the above results with simpler formulas:
Helper column: MONTHS = (MONTH([Date2]@row) - MONTH([Date1]@row))
Helper column: YEARS = YEAR([Date2]@row) - YEAR([Date1]@row)
Result column: Years / Months = IF(MONTHS@row < 0, (YEARS@row - 1) + " /" + (MONTHS@row + 12), YEARS@row + " / " + MONTHS@row)
RESULT: 62 / 11
Answers
-
Your Excel formula only gives years, not years and months. Here's a few options.
If Date1 is their birthday and Date2 is their request date, then you can get years by this:
=ROUNDDOWN(NETDAYS([Date1]@row, [Date2]@row) / 365, 0)
RESULT: 62
-------------------------------
If you want decimal years, use it without the rounding (you may be a few days off due to leap years):
=NETDAYS([Date1]@row, [Date2]@row) / 365
RESULT: 62.93
------------------------------
If you want years and months, it's more complicated, but leap years won't have an impact (the result is "YEARS / MONTHS"):
=IF(MONTH([Date2]@row) - MONTH([Date1]@row) < 0, ((YEAR([Date2]@row) - YEAR([Date1]@row) - 1) + " / " + (MONTH([Date2]@row) - MONTH([Date1]@row) + 12)), (YEAR([Date2]@row) - YEAR([Date1]@row)) + " / " + (MONTH([Date2]@row) - MONTH([Date1]@row)))
RESULT: 62 / 11
-------------------------------
If you don't mind 2 helper columns plus the result column, you can achieve the above results with simpler formulas:
Helper column: MONTHS = (MONTH([Date2]@row) - MONTH([Date1]@row))
Helper column: YEARS = YEAR([Date2]@row) - YEAR([Date1]@row)
Result column: Years / Months = IF(MONTHS@row < 0, (YEARS@row - 1) + " /" + (MONTHS@row + 12), YEARS@row + " / " + MONTHS@row)
RESULT: 62 / 11
-
Thank you so much! :) I was able to do my whole smartheet.
Regards,
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!