Skills Level: Intermediate I recently had to complete a form in Excel where the ID numbers of a group of learners were required, as well as their age. I had a list of their ID numbers ready, but age is not something that you can store along with other records since it is not static data and obviously changes every year. Despising forms and admin in general, I looked for a shortcut to calculate the age with as little effort as possible, avoiding manual calculations at all cost. Here is how in a few easy steps: Below is a list of ID numbers (these are in the format of a South African ID number, but they are not real…). The first 6 digits is the birth date of the individual (YYMMDD). Because the data that you are working with is uniform (all ID numbers have the same number of characters), you can easily extract or separate only the first 6 digits. Highlight the data set (or the whole column – it’s quicker), and select the Data tab, and then in the Data Tools group, select Text to Columns. A dialog box opens providing a few options for splitting your data into 2 or more columns. In this case, you will not use the delimit function to separate the characters, as there are no spaces or punctuation marks (full stop, comma, semi-colon etc) in your data. But because each record contains exactly 13 characters, you can split the data by Fixed Width. Tick this option, and select Next. The next window gives clear instructions on how to insert a break in your data. In this case, you want to separate the first 6 digits from the last 7. Simply hover your cursor between the 6th and 7th character of any one of the records, or the scale above it, and click to insert a line (or break). A line will appear where data will be split into different columns. Also, notice the instructions included on how to delete a break line (double click on the line) or move a break line (click and drag). Note that you can split data into multiple columns by inserting more lines to create more column breaks. Click next to continue to the 3rd step. In this step, you can change the format of the data. This is very convenient since the first 6 digits of an ID number on its own will not automatically be recognised as a date. Without the forward slashes in between Year, Month and Date, Excel will not recognise this data as a date. This step forces the data to be formatted in this way. Select the Date option and change the order accordingly to YMD. In the above step, the first six digits were highlighted and the date format only applied to these characters. You can set the appropriate format for the rest of the data (which will be in separate columns) too by highlighting the second set of characters and selecting the format that you wish to apply. In this case, we do not need the rest of the characters. No formatting is therefore needed. Click finish to complete the process. Below you can see how the data has been split. The data in the first column may seem strange at first. Just by looking at the first record (in A1), it now shows 27191 instead of the first 6 digits as in our original data which should be 740611. Remember how dates in Excel are stored as reference numbers relating to the number of days after 1/1/1900? 27191 therefore refers to 27 191 days after 1/1/1900. Refer to my previous blog about dates if you missed it. The format has therefore been correctly stored as a date from the previous step, but it is not displayed as a date. To display it in the correct format, highlight column A and change the formatting to date. Record A1 now changes to 11/6/1974, which corresponds to the original ID number starting with 740611. You now have a list of birthdates instead of ID numbers. You can get rid of the data in the second column. We will not need it for our calculations. To calculate the age of every learner as at a certain date (today or any other date), enter the date in any other cell (I am using Cell C1). If you might have to refer to this data in future, and want the age to be updated accordingly as at the future date when you use the same file again, try using the Today function. See the previous blog post about dates for an explanation on how it works. To calculate the age of someone today, subtract the date of birth from today’s date. This will give you a person’s age in days. =C1-A1 To change it to years of age, further divide by 365.25 (the .25 accounts for leap years every 4th year). =(C1-A1)/365.25 The result will usually be a decimal (except where the current date is on the learner’s birthday). To reduce the number of decimals to zero or to round the answer to the closest integer is not ideal. In this case, doing so will round the age up to 43, which is not wrong, but since this individual has not had his 43rd birthday yet, you may want his age to be indicated as 42 and not 43. This is where the TRUNCATE formula is useful. The formula explanation below is quite easy to understand. The truncate formula simply chops off the decimals as opposed to rounding to the nearest integer. I have pasted the first result as a value in two cells just to illustrate the function and compare it with the round function. See below the difference between the truncate formula and the round formula: The formula arguments for both are exactly the same: =TRUNC(number, [num_digits]) =ROUND(number, [num_digits]) Number refers to the cell that contains the number that you want to truncate / round. And [num_digits] refers to the number of decimals that you want to remain. In this case it will be zero (note that when you enter zero, and press enter and return to the formula, the zero may have fallen away, like above). The result is below: The truncate function has chopped off the decimals, while round has rounded to the closest integer.
To use this function in our formula, you need to embed the existing formula within the truncate function as follows: =TRUNC((C1-A1)/365.25,0) If you want to drag or copy the formula down, you need to make cell C1 (the one containing today’s date) absolute to prevent the reference to it from moving as you drag the formula downwards. (See lesson on absolute and relative references here). Simply place your cursor before or within the cell reference, and press F4 to make the cell absolute. Alternatively, you can insert the dollar signs ($) manually. =TRUNC(($C$1-A1)/365.25,0) Now you can drag your formula downwards to copy the formula in the rest of the cells to calculate the rest of the learners’ ages.
3 Comments
Carine
22/2/2017 22:40:58
Hi Len. Yes, the Datedif would also do the trick. I use the datedif function for simple calculations, but due to it being an unsupported function, and reports of it calculating incorrectly in certain instances, I prefer not to use it with more complex calculations.
Reply
scott rogerson
20/10/2017 20:45:58
I understand the need for these functions for Excel 2007 and prior versions, however, to solve this now, I would take the serial number and DOB and put them in a table. Next I would add it to the PowerPivot Data Model as well as any-other tables I need to use the date calculation on. Then I would present the data.
Reply
Leave a Reply. |
Carine HoughHi, and welcome to my blog :) I am an Excel enthusiast and want to help others keen on improving their own Excel skills. I hope you learn something useful on my blog. Categories
All
|