How to calculate age from DOB in Excel

Excel users frequently want to calculate age from date of birth. You can do this with a few simple steps in Easy Data Transform:

• Install Easy Data Transform on your PC or Mac, if you haven’t done so already. It will only take a minute. There is a fully functional free trial and you don’t have to give us your email or sign up to anything.

1. Start Easy Data Transform. Make sure the Auto Run button is pressed in.

• Drag the Excel spreadsheet with the dates of birth onto Easy Data Transform. If there are multiple sheets, you will be asked which one to input. A pink input item will be added.

• You now need the date in ISO format (yyy-mm-dd) for the calculation. You can skip the next 2 bullet points if the date is already in this format.

• Ensuring the pink input item is selected, click the Date Format button in the Left pane.

• The Date Format transform is added and selected. In the Right pane check the date of birth column and change Format from to the current date format and Format to to the ISO standard yyyy-MM-dd. The date of birth column should now change to the new format. No need to run anything. Your original spreadsheet has not been changed. Ensure the Date Format transform is selected.

• Click the Javascript button in the Left pane. You may need to scroll the Left pane to see it. If the button isn’t showing, check show advanced in the Left pane.

• The Javascript transform is added and selected. In the Right pane set New column name to Age and Javascript to the following:

return new Date() - new Date( $(DOB) );

But replace DOB with the name of your date of birth column. Click Evaluate. The Age column should now show the difference (in milliseconds) between the current date and the date of birth.

• Ensuring Javascript transform is selected, click the Units button in the Left pane.

• The Units transform is added and selected. In the Right pane check the Age column and set Dimensions to Time, From to Milliseconds and To to Years. Age is now shown in years.

• Ensuring the Units transform is selected, click the Num Format button in the Left pane.

• The Num Format transform is added and selected. In the Right pane check the Age column, set Format to f and Precision to 0.

• Click the To File button in the Left pane (scroll to the bottom of the Left pane). A window will appear. Set the new file name and location. Select the file type, e.g. Excel .xlsx or .xls.

• A green output item is added and selected.

• The Excel file is created immediately at the chosen location, no need to ‘run’ anything. It will be updated any time you change something ‘upstream’.

• File>Save this series of transformations if you might want to use it again.

If you need to show duplicates in lots of Excel sheets you can do it in a single operation using the batch processing feature.

Easy Data Transform allows you to combine multiple transforms to quickly and easily create many different data transformations.