+2Hello 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.
 
Best answer by TheTimeSavingCo
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:
+31Hi 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:
+2Hi 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:
Awesome and thank you. I appreciate this.
+9Hi 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:
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?
+31Hi 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?
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
+2Awesome and thank you. I appreciate this.
Howdy, today when I logged in the result shows a negative integer. This was here the previous two days.
+9Howdy, today when I logged in the result shows a negative integer. This was here the previous two days.
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.
+2Hi 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.
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.
+2Hi 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:
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)".
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.