Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Feb 25, 2019 08:01 PM
I’m organizing a list of companies and trying to calculate how many years they’ve been in business.
Some companies are still open/in existence, and some businesses have closed/shut down.
Ideally I’d like to have 3 fields:
I’m trying to figure out how to write a formula that basically states the following:
Age = IF End Date is null (TODAY - Start Date), ELSE (End Date - Start Date).
I’d also like to figure out how to make all 3 of those fields on show “years” as appose to the whole date field (month/day/year). Any guidance is much appropriated!
Feb 25, 2019 08:46 PM
Figured it out, here’s the formula and example below:
“Started”: Year the company started - (number field)
“Ended”: Year the company closed - (number field)
“Age”: How many the years the company has existed - (formula field)
IF({Ended} = BLANK(), DATETIME_FORMAT(TODAY(),‘YYYY’)-{Started}, {Ended}-{Started}) & " yrs old"
RESULT
Feb 26, 2019 04:23 AM
Two notes:
YEAR()
function to the get the year of a date (including NOW()
)DATETIME_DIFF()
function to use instead of the math calculation.-{Started}
part.I’d do:
DATETIME_DIFF(Started,IF(Ended, Ended, NOW()), 'y') & ' yrs old'
Feb 26, 2019 09:15 AM
Thanks for the suggestion, I’ll plug that in and give it a try!
Feb 26, 2019 09:42 AM
I’ve done the formula without testing, but I hope it’s correct because it’s easy. If not, just recreate it.