Skip to main content

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:





  1. Start Date: The year the company opened for business


  2. End Date: The year the company closed for business (if the business actually closed down)


  3. Age: How many years the company has existed either up to today OR to the “End Date” if there is a value placed in that field.




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!

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


Two notes:





  • You have YEAR() function to the get the year of a date (including NOW())


  • You also have DATETIME_DIFF() function to use instead of the math calculation.


  • Do the IF check inside the calculation so you don’t have to repeat the -{Started} part.




I’d do:



DATETIME_DIFF(Started,IF(Ended, Ended, NOW()), 'y') & ' yrs old'

Two notes:





  • You have YEAR() function to the get the year of a date (including NOW())


  • You also have DATETIME_DIFF() function to use instead of the math calculation.


  • Do the IF check inside the calculation so you don’t have to repeat the -{Started} part.




I’d do:



DATETIME_DIFF(Started,IF(Ended, Ended, NOW()), 'y') & ' yrs old'


Thanks for the suggestion, I’ll plug that in and give it a try!

Thanks for the suggestion, I’ll plug that in and give it a try!


I’ve done the formula without testing, but I hope it’s correct because it’s easy. If not, just recreate it.

Reply


Terms & Conditions