Calculate Age in Excel
February 27, 2018
Change a setting to allow multiple filters, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.
Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!
There are three kinds of filters that you can use on a pivot table field:
By default, Excel only lets you use one of those filters at a time. So, you could filter a Month field to
If you need to use both filters at once (top 3 months, from Jan-June), change a setting in the pivot table:
See more pivot table filter tips on my website, and watch a short video on applying multiple filters.
Last week at the Olympics, the USA team beat Canada in the women's hockey final. To compare the two teams, I got the team rosters, which have player names, height, date of birth, etc.
What formulas would you use to get each player's age, and height in feet (5.58 instead of 5'7)? Mine are below the screen shot.
Here are the formulas that I used:
For the age, I used DATEDIF, but be careful with that function - it can give incorrect results.
=DATEDIF([@DOB],TODAY(),"y")
The height was listed as feet and inches (5'7), and this formula converts it to feet (5.58).
=SUM(LEFT([@Height],1), RIGHT([@Height], LEN([@Height])-2)/12)
Here are a couple of Excel articles that you might find useful or interesting.
Excel Tips - Neil Patel shares 7 advanced Excel tips. The examples show how to analyze website data, but are useful for other types of Excel projects too. NOTE: Tip #4 is a function for Google Sheets, not Excel. (Level - Intermediate)
Data Viz - On the Flowing Data blog, Nathan Yau posted a link to Beginners Guide to Visualization literacy. Click that link, then click the "static version" link. (Level - All)
Also see: My Excel Products || Excel Events || Previous Issues || Excel Humour
My sister came to visit last week - she was attending an engineering conference here in Mississauga. She brought along a bottle of Sibling Rivaly red wine, from nearby Niagara-on-the-Lake. Fortunately, we didn't have any sibling rivalry during her visit - just an enjoyable time!
During the conference sessions, she made Sketchnotes instead of written notes, so I want to learn more about that technique. Have you ever tried visual note taking?
That's it for this week! If you have any comments or questions, send me an email.
NOTE: For the online version, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2018/20180227ctx.html
I'll also post any article updates or corrections there.
Debra Dalgleish
ddalgleish @ contextures.com
P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.
Last updated: May 4, 2018 3:27 PM