Microsoft Office Training London 020 7920 9500
Excel Calculating Age from Date of Birth – It’s easy to calculate someone’s age from their date of birth if you know about Excel’s DATEDIF function, unfortunately it’s easy to miss this function as it is not documented. I am baffled as to why it is never included in the list of Excel functions.
To have a formula that updates itself as time goes on you need to get the current date into your formula and you can do this using either the NOW or TODAY functions. There’s no substantial difference between the two functions for this calculation; NOW gives you the current date and time, whereas TODAY just gives the current date. When you enter the function into your formula remember to include the brackets like this, NOW() or TODAY().
So, to calculate someone’s age in years make sure that you have entered their date of birth correctly as a proper Excel date (using slashes or dashes) and then enter the formula:
=DATEDIF(C3,NOW(),”y”)
Where C3 is the cell containing the date of birth. The interval value “y” must be entered in double quotation marks. The age is calculated in whole calendar years and will update on the subject’s birthday. If you want to calculate the exact age and include the months or even the days then you have to change the interval value. Refer to the table of interval values below for further details.
The function is not documented so I am going to have to be a bit boring and do the documentation here in case you want to Bookmark this page. I always find the interval values a bit difficult to remember; “y” for year is dead easy but I struggle to remember the others. The only time you see any indication that DATEDIF exists is when you type it into a cell and Excel pops up a ghostly apparition as you enter the first bracket.
| Interval | Description |
|---|---|
|
m |
Complete Calendar Months |
|
d |
Number of Days |
|
y |
Complete Calendar Years |
|
ym |
Months excluding Years |
|
yd |
Days excluding Years |
|
md |
Days excluding Years and Months |
The first three interval values give the gross number of years, months or days between the two dates and the last three interval values work out the days and months as if the dates were in the same year and are very useful for calculating the fragments of months and days where the date difference needs to be calculated precisely.
You must enclose the interval in double quotation marks if you are including it directly in the formula or you can enter it into a cell without quotation marks and use the cell reference of the cell in the formula instead.
If Date1 is later than Date2, DATEDIF returns a #NUM! error. If either Date1 or Date2 is not a valid date, DATEDIF returns a #VALUE! error. If Interval is not one of the items listed in the table, DATEDIF returns a #NUM! error.
To get the best out of Excel formulas you’ll need to have a good, working knowledge of Excel functions. Come along on our dedicated Excel functions course and discover what Excel formulas can do for you.
Call 020 7920 9500 now for details.
The SUMPRODUCT function is one of those hero functions that, once discovered, you wonder how you ever managed without. It does not just do “what it says on the tin”, there’s a lot more to it than that. In particular, it’s one of the most powerful and flexible filter functions in Excel. And so much better than SUMIF or SUMIFS.
Our Excel double click tricks are some of those little things that make your life so much easier. You probably know most of them already. Or do you? I think that anyone who uses Excel regularly should know them.
Complicated text formulas using either ampersands and the CONCATENATE function are the bane of our life. Not any more! Excel new text functions will really help us nail those text formulas. We’ll be looking at the CONCAT function and the TEXTJOIN function.
Usually the formulas you need for percentages and differences are quite straightforward: divisions for percentages and a minus sign to take one value from another. But there are pitfalls for the unwary which we shall explore.
I think the Excel FILTER function does the filter job better than AutoFilter. It’s a live formula and an extraction, you don’t have to filter your data in place. There’s no need for that clunky Advanced Filter…
You can use the Excel UNIQUE function to extract a list of unique or distinct items from a range of Excel cell values. It’s really easy. See how to sort the results automatically when the data changes…