{"id":441,"date":"2011-10-17T14:53:52","date_gmt":"2011-10-17T14:53:52","guid":{"rendered":"http:\/\/www.microsofttraining.net\/b\/exceltraining\/?p=441"},"modified":"2023-12-30T23:13:14","modified_gmt":"2023-12-30T23:13:14","slug":"how-to-calculate-your-age-in-days-and-whole-years-in-excel","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/how-to-calculate-your-age-in-days-and-whole-years-in-excel\/","title":{"rendered":"How to: Calculate your age in days and whole years in Excel"},"content":{"rendered":"<p>This technique can be used to calculate the difference between any two dates.\u00a0 The worked example shows calculating age and showing this in days and in whole years.\u00a0 It involves using the TODAY and TRUNCATE functions and nesting several functions together.<\/p>\n<ul>\n<li>Create a table for showing date of birth for required people \u2013 start this in row 3<\/li>\n<li>In cell A1, input the Today function (the today function is always <strong>=TODAY() <\/strong>, but can also be inserted using the Formulas tab and is in the Date &amp; Time group)<\/li>\n<\/ul>\n<p>Your Sheet will now look something like this:\u00a0<a href=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2011\/10\/birthday1.png\"><img decoding=\"async\" class=\"aligncenter size-full wp-image-442\" src=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2011\/10\/birthday1.png\" alt=\"\" width=\"327\" height=\"147\" \/><\/a><\/p>\n<ul>\n<li>To calculate age in days, subtract the date of birth from todays date. To enable you to copy the formula to all rows you should use an absolute cell reference to cell A1.\u00a0 Your formula will read <strong>=$A$1-A4<\/strong><\/li>\n<li>To convert this into years, you need to divide the age in days by 365.25, so your formula will read <strong>=C4\/365.25<\/strong>\u00a0 Were you to use formatting to turn this into whole year (i.e. format the cell to a number showing 0 decimal places, anyone over 6 months towards their next birthday would become a year older!\u00a0 So we need to truncate the number, removing everything after the decimal point and only using the whole years.<\/li>\n<li>The Truncate function is <strong>=TRUNC(<em>Number to truncate, no of decimal places to keep).<\/em><\/strong> In the example we want to truncate C4\/365.25, but haven\u2019t yet put the formula into a cell, as I want just the one formula, so my formula in D4 will be <strong>=TRUNC(C4\/365.25,0)<\/strong><\/li>\n<li>If I wanted to only display age in full years, I could not hide column C or I could have put the whole calculation in one cell, giving a formula of \u00a0<strong>=TRUNC(($A$1-A4)\/365.25,0)<\/strong><\/li>\n<li>Example 1 below shows the two column solution and Example 2 the one column solution\n<figure id=\"attachment_443\" aria-describedby=\"caption-attachment-443\" style=\"width: 623px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2011\/10\/birthday2.png\"><img decoding=\"async\" class=\"size-full wp-image-443\" src=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2011\/10\/birthday2.png\" alt=\"\" width=\"623\" height=\"144\" \/><\/a><figcaption id=\"caption-attachment-443\" class=\"wp-caption-text\">Example 1<\/figcaption><\/figure>\n<figure id=\"attachment_444\" aria-describedby=\"caption-attachment-444\" style=\"width: 508px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2011\/10\/birthday3.png\"><img decoding=\"async\" class=\"size-full wp-image-444\" src=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2011\/10\/birthday3.png\" alt=\"\" width=\"508\" height=\"143\" \/><\/a><figcaption id=\"caption-attachment-444\" class=\"wp-caption-text\">Example 2<\/figcaption><\/figure>\n<p><figure id=\"attachment_445\" aria-describedby=\"caption-attachment-445\" style=\"width: 269px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2011\/10\/birthday4.png\"><img decoding=\"async\" class=\"size-full wp-image-445\" src=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2011\/10\/birthday4.png\" alt=\"\" width=\"269\" height=\"142\" \/><\/a><figcaption id=\"caption-attachment-445\" class=\"wp-caption-text\">On 17th October 2011, these are the answers<\/figcaption><\/figure><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This technique can be used to calculate the difference between any two dates.\u00a0 The worked example shows calculating age and showing this in days and in whole years.\u00a0 It involves using the TODAY and TRUNCATE functions and nesting several functions together. Create a table for showing date of birth for required people \u2013 start this [&hellip;]<\/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":[79,83,171,482],"class_list":["post-441","post","type-post","status-publish","format-standard","hentry","category-excel-training","tag-calculate-age","tag-calculate-with-dates","tag-excel","tag-sf"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/441","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=441"}],"version-history":[{"count":1,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/441\/revisions"}],"predecessor-version":[{"id":6762,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/441\/revisions\/6762"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=441"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=441"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=441"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}