Tobias Eisermann
Formulas to calculate the educational age in Excel, Google Docs and OpenOffice
If you work as a nursery teacher or do an internship in a kindergarten you will probably need to notate the children's ages in a special way: years;months. Because it can be really annoying to do this over and over again for many dates of birth, I figured out the formulas to use in Excel, Google Docs and OpenOffice.
Table Structure and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Date of birth | Years;Months | Today: | 2021-05-02 |
| 2 | Steve | 2017-03-02 | 4;2 | ||
| 3 | Haley | 2017-08-16 | 3;8 |
Insert the formula into cell C2 (and pull down as much as you need). The formula reads the date of birth from cell B2 (and below, if pulled down) and the date you enter in cell E1 ($E$1 in the formula). If you want E1 to be always "today", type =NOW() (Deutsch: =JETZT()) into it and hit Enter.
Formula for Excel and Google Docs
=ROUNDDOWN( DATEDIF( B2; $E$1; "M" / 12; 0 ) & ";" & IF( MOD( DATEDIF( B2; $E$1; "M"; 12 ) = 0; "0"; MOD( DATEDIF( B2; $E$1; "M"; 12 ) )
Formula for OpenOffice
English
=ROUNDDOWN( MONTHS( B2; $E$1; 0 ) / 12; 0 ) & ";" & IF( MOD( MONTHS( B2; $E$1; 0 ); 12 ) = 0; "0"; MOD( MONTHS( B2; $E$1; 0 ); 12 ) )
Deutsch
=ABRUNDEN( MONATE( B2; $E$1; 0 ) / 12; 0 ) & ";" & WENN( REST( MONATE( B2; $E$1; 0 ); 12 ) = 0; "0"; REST( MONATE( B2; $E$1; 0 ); 12 ) )