Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Need formula to return fractional years between dates

1516 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Dennis_Stevens
4 - Data Explorer
4 - Data Explorer

I need to count the number of fractional years between dates. I’m currently using this formula to count the number of days between dates:

DATETIME_DIFF({To},{From},‘days’)

and this formula works great for the number of days between dates but what I really need is fractional years between dates, such as 1.1, 1.5, etc, such as the YEARFRAC function does in Excel. If I change ‘days’ to ‘year’ I get whole number results like 0, 1 or 2. I need to show fractions of a year.

Suggestions anyone?

4 Replies 4

Hi @Dennis_Stevens - use ‘months’ in your date diff formula, then divide the result by 12:

Screenshot 2019-06-30 at 16.28.15.png

(DATETIME_DIFF(To, From, 'months'))/12

JB

Dennis_Stevens
4 - Data Explorer
4 - Data Explorer

Thank you very much for your help. The formula, however, did not work for me. This is the formula I used with your suggestion:

(DATETIME_DIFF(To,From,‘months’))/12

It returned whole numbers instead of fractional ones. Any follow ideas? And thanks again very much for trying to help.

Ah, OK, you need to look at the formatting option on the formula field - change this from integer to decimal:

Screenshot 2019-07-01 at 06.28.04.png

JB

That’s it! Perfect; thank you very much. I’m new to Airtable and had a project ready to go so I’m heading out without much experience with it yet. So far, I love.

Thanks again.

Dennis