Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 30, 2022 01:00 PM
Hello all my first post and i'll probably be embarrassed if answered but, I have a formula in excel that tells me how many total years and then months and then days since someone has been hired and I'm not quite there with Airtable as I am getting how many total years and then total months and then total days.
Solved! Go to Solution.
Dec 31, 2022 06:40 AM
Hi KenM, try this:
IF(
Date != "",
CONCATENATE(
DATETIME_DIFF(TODAY(), Date, 'years'),
""
) &
" years, " &
CONCATENATE(
MONTH(TODAY()) - MONTH(Date),
""
) &
" months, " &
CONCATENATE(
DAY(TODAY()) - DAY(Date),
""
) &
" days"
)
Resulting in this:
Jan 04, 2023 10:45 AM
Thanks again and I have adjusted my brain. I can live with this. I just now view it as a countdown to their anniversary instead of an accrued date since their hire date.
Dec 31, 2022 06:40 AM
Hi KenM, try this:
IF(
Date != "",
CONCATENATE(
DATETIME_DIFF(TODAY(), Date, 'years'),
""
) &
" years, " &
CONCATENATE(
MONTH(TODAY()) - MONTH(Date),
""
) &
" months, " &
CONCATENATE(
DAY(TODAY()) - DAY(Date),
""
) &
" days"
)
Resulting in this:
Dec 31, 2022 07:58 AM
Awesome and thank you. I appreciate this.
Dec 31, 2022 11:26 AM
Hi Adam - nice formula however when I've used Today() to calculate the years between 1/1/2023 and 1/1/2022 it comes out as 0 years instead of 1. If I use Now() it returns the correct value. I think Today() returns GMT and Now() returns your local time?
Jan 01, 2023 04:37 AM
From what I understand, TODAY() returns the date without the time, and it defaults to 12 AM. In addition, I think you might be encountering a timezone issue as dates on Airtable are stored as GMT+0, resulting in behaviour you saw
Jan 02, 2023 01:28 PM
Jan 03, 2023 11:26 AM
Hi Ken - calculating days months and years between dates looks like it should be simple but the more you look the more you realise the complexity of it. I have my doubts that it's possible using an airtable formula field.
Each month has a different number of days so you have to know how many of each month there is. For example 1 Jan 2023 to 1 Feb 2023 is a total of 31 days but equals a month. 1 Feb 2023 to 1 Mar 2023 is only 28 days but also equals a month. So given that a unit "month" can be multiple values you can't just subtract parts of the dates to get the result. As you've seen "2 Jan" - "31 Dec" gives you negative 29 days and negative 11 months. On top of all that every 4 years you have a leap year to consider. Another consideration is do you include the end date and therefore add 1 day on?
I think your best bet is to write a script - have a look at https://github.com/codebox/moment-precise-range
Either that or just use the number of days, ignoring months and years.
Jan 04, 2023 10:45 AM
Thanks again and I have adjusted my brain. I can live with this. I just now view it as a countdown to their anniversary instead of an accrued date since their hire date.
Jan 07, 2023 04:02 AM - edited Jan 07, 2023 05:31 PM
Hi Adam_TheTimeSav and thank you for the new formula as I am way far out of my knowledge zone as a beginner. Thank you for your valuable time. I went ahead and put both formulas side by side in my roster database and will evaluate over a couple weeks as I see the days and months click away. Your new formula is "Practice - Accrued (1)".