Tobias Eisermann | Grafik, Web & Sound Designer

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 ) )