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.
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?
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
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 07, 202304:02 AM - edited Jan 07, 202305: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)".