{"id":1947,"date":"2014-11-18T10:57:58","date_gmt":"2014-11-18T10:57:58","guid":{"rendered":"http:\/\/www.microsofttraining.net\/b\/?p=1947"},"modified":"2023-12-31T00:04:04","modified_gmt":"2023-12-31T00:04:04","slug":"how-to-calculate-age-from-date-of-birth-using-excel","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/how-to-calculate-age-from-date-of-birth-using-excel\/","title":{"rendered":"How to calculate age from date of birth using Excel"},"content":{"rendered":"

An easy way on how to calculate\u00a0the age from date of birth using Excel formula<\/h2>\n

Here we\u2019ll look at how using the Today()<\/strong> function along with a less well known function, Datedif()<\/strong>, calculates the age from a date of birth. Even if this isn’t a pressing need of yours the example below is a handy demonstration of the usefulness of these two functions in an Excel formula to calculate age from a date of birth.<\/p>\n

The following formula uses both these functions to achieve the desired result.<\/p>\n

=DATEDIF(B4,TODAY(),”y”)<\/strong>
\nwhere \u201cB4\u201d is the actual cell containing the date of birth.<\/p>\n

Here’s\u00a0the formula in action.<\/p>\n

\"How<\/a>
Formula using the Datedif() & Today() functions<\/figcaption><\/figure>\n

Tip:<\/strong>\u00a0You can also express the age as months, or even days by simply changing the \u201cy\u201d in the above formula to \u201cm\u201d or \u201cd\u201d respectively.<\/p>\n

There are many ways to achieve the same result and the example above is one such\u00a0way on how to calculate age from date of birth using Excel.<\/p>\n

How Excel stores dates:<\/strong><\/h3>\n

Dates and time are some of the most common types of data that people use in Excel and the way that Excel stores dates is quite different to how we would imagine.<\/p>\n

Instead of storing a date as day, month, year (01\/01\/1900,\u00a0for example) Excel actually allocates a serial number (which is generated from working out how many days have elapsed since the year 1900 to that date). \u00a0And yes, any date before 1900, as far as Excel is concerned, just doesn’t exist!\u00a0This is why if you don\u2019t have the correct cell formatting for date data you get a number bearing no relation to the\u00a0date!<\/p>\n

Additional resources:<\/strong><\/p>\n

You can\u00a0view answers to actual Excel users’ questions related to time and date on the following links:<\/p>\n

https:\/\/www.stl-training.co.uk\/post-6898-calculated-age-person-each.html<\/a><\/p>\n

https:\/\/www.stl-training.co.uk\/post-23813-calculating-ages-dates.html<\/a><\/p>\n

For a more in-depth look at how to use dates and times in Excel:<\/p>\n

http:\/\/support.microsoft.com\/kb\/214094<\/a><\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

An easy way on how to calculate\u00a0the age from date of birth using Excel formula Here we\u2019ll look at how using the Today() function along with a less well known function, Datedif(), calculates the age from a date of birth. Even if this isn’t a pressing need of yours the example below is a handy […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4],"tags":[51,147,182,199,264,287,303,537,610],"class_list":["post-1947","post","type-post","status-publish","format-standard","hentry","category-excel-training","tag-age-calculation","tag-datedif-function","tag-excel-age-formula","tag-excel-formula-to-calculate-age-from-date-of-birth","tag-formula-to-work-out-a-birthday-in-excel","tag-how-excel-stores-dates","tag-how-to-use-dates-and-times-in-excel","tag-today-function-in-excel","tag-work-out-age-from-dob-in-excel"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/1947","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=1947"}],"version-history":[{"count":1,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/1947\/revisions"}],"predecessor-version":[{"id":6848,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/1947\/revisions\/6848"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=1947"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=1947"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=1947"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}