Skip to main content

Calculate Age or Years Elapsed in an Apple Numbers Sheet

Often it is useful to show a person's age or years elapsed since a start date.  For example:
Hire Date: 4/1/2012 - Years of service: 1.5
Here's a formula for Numbers that will do the trick:
=IF(ISBLANK(cellReference),"",DATEDIF(cellReference,TODAY(),"D")/365.2425)
Replace the cellReference with the actual cell reference. i.e.: (A1) 
So here's the breakdown:

=IF(ISBLANK(cellReference),"" --- this checks to see if there is a start date in your referenced cell.  For example say your spreadsheet has a cell (A1) that holds a date of birth, but it is not yet referenced, this will result in an empty string. (Blank cell)

Otherwise, it calculates the years: DATEDIF(cellReference,TODAY(),"D")/365.2425)

 DATEDIF compares two dates.
The first date is your cell reference i.e.: (A1)
The second is the current date according to your computer, iOS device: TODAY() returning the Day - "D"
Then the difference is divided by 365.2425 to get the number of years.  Set your cell to result in a number with one decimal place.

There you go!




Labels

Labels:

Comments

Post a Comment

Popular posts from this blog

Do You Need to Defragment a Mac’s Hard Drive?

--> Do You Need to Defragment a Mac's Hard Drive? About Focus on Macs In my mailbag this week, I found a couple of questions about defragmenting a Mac's hard drive. This question usually comes from new Mac users, or individuals who switch to the Mac from the Windows environment, where disk defragmentation utilities abound. Some individuals want to know which third-party disk defragmentation app they should use, or wonder why there is no defrag tool in OS X. Courtesy of Apple OS X does have disk defragmentation capabilities, but they're built into the system rather than a separate tool. Since OS X 10.2, Apple has included automatic defragmentation in the Mac OS. In essence, the Mac OS has built-in safeguards that attempt to prevent file fragmentation from occurring; it's also able to repair fragmentation, should it occur. This means that for the average Mac user, there really is no reason to worry about disk defragmentation, at least not as ...
1 comment
Read more

Learn To Code

Even if just to get a better understanding of how computers work or learning how to customize your browsing experience, knowing the basics of coding opens ones eyes to possibilities once only known by a few.  Learn To Code Planet Cocoa If learning to program is even a minor goal for you,  Code Year (via  Brent Simmons ) might be just the encouragement you need. They promise to email you on a weekly basis with coding lessons to help you achieve your goal. I'm one of those computer programmers who downplays the difficulty of the profession, because "if I can do it, anybody can do it!" On the other hand, I have faced challenges that made me question whether I'm vaguely qualified for the job. What it boils down to is that programming is both incredibly simple and impossibly hard, like so many important things in life. There was a time when nobody knew how to write literary prose. The geniuses who invented it shared their special tool with a ...
Post a Comment
Read more

Tips: Delete duplicate entries in "Open With..." dialog in Finder

Duplicates!!! When you control+click (or right click if you have enabled that option) on Mountain Lion, and there seems to be several duplicate applications listed and/or apps you no longer use, here is a Terminal shell script that will fix that.  Just copy and paste the code below in a Terminal window. /System/Library/Frameworks/CoreServices.framework/Versions/A/Frameworks/LaunchServices.framework/Versions/A/Support/lsregister -kill -r -domain local -domain user (Terminal can be found using Launchpad or Spotlight) After pasting, hit the Return key and allow time to run, then type "killAll Finder" without the quotes, and Return.  This will rebuild the "Open With..." menu.   If you do not see an immediate effect on the lists, restart your Mac.   Should be no need to restart. (Thanks, JK) Duplicates and old apps gone! If you wish, you can make a Service that will do it using Automator as well: Open Automator (Launch Pad or Applications folder) Create...
2 comments
Read more