Accrued Date Formula

[KenM]

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.

 

[TheTimeSavingCo · Accepted solution]

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:

[KenM]

Awesome and thank you. I appreciate this.

[John_B2]

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?

[TheTimeSavingCo]

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

[KenM]

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

[John_B2]

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.

[KenM · Accepted solution]

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.

[KenM]

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