Hi, I am trying to count the number of months from the Start Date till today. The actual day of the 'Start Date' doesn't matter. For example, if 'Start Date' is 9/20/23 and today is 12/11/23, I want to count the number of months that have passed: Sept, Oct, Nov, Dec = 4. I have tried this formula:
Hi @Sharon_Visser,
I think this is better.
IF({Start Date},MONTH(TODAY())-MONTH({Start Date}))
Hi @Sho , I tried this and it's giving me an answer of 3 instead of 4. I think it's taking into account the days, and not just looking at months. I'm assuming someone worked even 1 day each month between the Start and today, so then I need to count the month. Thank you.
In this case, if the "Stat date" input means one month, so +1 is sufficient.
IF(
{Start Date},
MONTH(TODAY())-MONTH({Start Date})+1
)
In this case, if the "Stat date" input means one month, so +1 is sufficient.
IF(
{Start Date},
MONTH(TODAY())-MONTH({Start Date})+1
)
Hi again, I do appreciate your help, but this version only seems to work within the same year. As soon as it crosses a year boundary it's not right. If Start Date = '9/20/23', and Today is '1/10/24', the result is -7 rather than 5 (Sept, Oct, Nov, Dec, Jan). I made test fields for 'Test Start', and 'Test Today' so I can plug in different values. My formula is:
Sorry, it was a bad idea.
I should have used DIFF properly.
DATETIME_DIFF(
DATETIME_FORMAT(TODAY(), "YYYY-MM"),
DATETIME_FORMAT({Date}, "YYYY-MM"),
"month"
)
Airtable sometimes misinterprets dates, so this might be better.
DATETIME_DIFF(
DATETIME_PARSE(DATETIME_FORMAT(TODAY(),"YYYY-MM"),"YYYY-MM-01"),
DATETIME_PARSE(DATETIME_FORMAT({Date},"YYYY-MM"),"YYYY-MM-01"),
"month"
)
Thank you, the solution using DATETIME_DIFF and DATETIME_PARSE works - though I had to add "+1" to the end of the formula. I did some testing crossing year boundaries and it works. This is a really helpful formula, I appreciate your help!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.