When you develop a financial model or any other management tool in Excel, you need dynamic calculations that update automatically. So, when you have to work with the age of certain individuals, you have to make sure that these individuals will have aged a year when you use your tool in a year’s time! To enable an automatic age calculation, you’ll need to work with the individuals’ dates of birth.
Dynamic age calculation in Excel
In the following example, in cell B1, we’ve first inserted the TODAY() function to determine the current date. When you open your file, you’ll then always see the current date in cell B1. No action is required to update.
We then inserted a table with the names of the individuals, followed by their dates of birth (which shouldn’t change over time!). Finally, we’ve inserted a last column, in which we’ve written a formula to calculate the age of the individual in question, based on the current date. This formula is written as follows: ARRONDI.INF(($B$1-C4)/365,0) or in English ROUNDDOWN(($B$1-C4)/365,0). It asks Excel to subtract the date of birth from the date of the day (which gives an answer in days) and then divide this number by 365 (to obtain the number of years). Finally, to avoid having to deal with decimals, we ask Excel to round down the result.
This means that no matter when you use your tool, the age of individuals will always be recalculated according to the current date.
Vous aimeriez créer des états financiers prévisionnels complets et dynamiques dans Excel, capables de se moduler à vos hypothèses, afin d’éclairer vos décisions d’affaires ? Apprenez les meilleures pratiques avec nos formations en modélisation financière. |
|---|
However, because of the reality of leap years, on rare occasions the calculation will be slightly off. For this reason, it might be even more interesting to use the following function:
- In French = DATEDIF(C4;aujourdhui(), “a”)
- In English=DATEDIF(C4,today(), “y”)
Further training
Are you as good at Excel as you think you are? Take the test! You can then follow the Excel – Upgrade which can serve as a warmup for professionals who already use Excel in their jobs and want to go further, and is also a recommended prerequisite for the Excel – Dashboards (level 1), Excel O365 – Welcome to the modern age! and above all, Excel – Financial modeling (Level 1).
Here are a few comments from learners who have taken the Excel – Mise à niveau course:
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
