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