# Accrued Date Formula

Solved
2113 8
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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.

2 Solutions

Accepted Solutions
18 - Pluto

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:

5 - Automation Enthusiast

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.

8 Replies 8
18 - Pluto

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:

5 - Automation Enthusiast

Awesome and thank you. I appreciate this.

6 - Interface Innovator

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?

18 - Pluto

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

5 - Automation Enthusiast

Howdy, today when I logged in the result shows a negative integer. This was here the previous two days.

6 - Interface Innovator

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.

5 - Automation Enthusiast

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.

5 - Automation Enthusiast

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)".