Germany Vacation Entitlement in Excel: Formula & Limits

The base formula: converting 24 working days to your own week

Germany's Federal Vacation Act (Bundesurlaubsgesetz, BUrlG) sets minimum vacation at 24 working days (Werktage) a year and assumes a six-day week (§ 3 BUrlG). For a five-day week or another pattern, the Federal Labor Court (Bundesarbeitsgericht, BAG) converts those 24 working days in proportion to the 260 or 312 possible working days of a six-day year: 24 working days times the employee's own working days in the year, divided by 312, which simplifies to 24 times working days per week divided by 6.

In Excel that is a single cell: =24*B2/6, with B2 holding the working days per week. For a five-day week that gives 24 Werktage × 5 ÷ 6 = 20 Arbeitstage, and proportionally less for a three-day week. Most spreadsheets get this part right, because it needs no case distinction at all and is easy to double-check with a pocket calculator.

Waiting period and partial entitlement: one formula quickly becomes three

Full entitlement arises only after six months of employment, the so-called waiting period (Wartezeit, § 4 BUrlG). In Excel, the end of the waiting period can be computed with =EDATE(B1,6)-1, with B1 holding the start date. For a start or an end within the same calendar year, the law then distinguishes three cases, each granting one-twelfth of the annual entitlement per full month (§ 5(1) BUrlG): the waiting period cannot be completed within the year of starting, the employment ends before the waiting period is met, or it ends after the waiting period is met but within the first half of the calendar year.

That case distinction alone turns one line into a nested IF with several conditions: the start of the year, the end of the year, the end of the waiting period, and whether the start or end date falls within the current year all have to be checked against each other before it is even clear which of the three cases applies.

Where the rounding rule in § 5(2) BUrlG breaks a formula

The part most homemade spreadsheets get wrong is the rounding itself. The law rounds a fraction up to a full day only once it reaches at least half a day; smaller fractions explicitly stay as fractions (§ 5(2) BUrlG). A formula built as =ROUNDUP(fraction,0), the obvious first attempt, rounds up every fraction instead, even a very small one.

Two examples with a five-day week show the difference: starting on September 1, 2027 gives 4 full months and a raw fraction of 6.67 days, which rounds up to 7 days because the remainder is at least half a day. Starting on November 1, 2027 gives only 2 full months and 3.33 days instead, and that is where it stays: 3.33 days, with no rounding up to the next whole number.

The correct formula therefore needs a condition: =IF(MOD(fraction,1)>=0.5,ROUNDUP(fraction,0),ROUND(fraction,2)). Without that distinction, a spreadsheet grants one day too many in plenty of cases, subtly enough that it only surfaces during an audit.

Three worked examples with a five-day week, 2027

The second half of the year: the costliest mistake in homemade sheets

The third case in § 5(1) BUrlG only covers someone who leaves after the waiting period is met within the first half of the year, that is, by June 30. If someone with a long-completed waiting period leaves only in the second half of the year, none of the three cases applies, and the full statutory annual entitlement stands, with no proration at all.

Many homemade formulas prorate anyway, usually because they only check the start and end dates against the current year without also comparing the end date to June 30. With an end date of September 30, 2027 and a long-completed waiting period, the entitlement stays at 20 days, the full amount for a five-day week, not a prorated figure.

Working days per week is not the same as working days on a calendar

Working days per week in this formula is a plain weekly pattern, how many days someone regularly works, independent of public holidays or a specific period. That is a different concept from the actual working days in a given month or project, even though the two terms sound alike and get mixed up easily in a spreadsheet.

For that second question, how many working days January 2027 actually has in a given state once weekends and public holidays are deducted, there is a separate working days calculator: 20 working days out of 31 calendar days in Hesse. The two calculators complement each other but do not replace one another.

What a simple Excel formula rarely gets right

• Rounding only from half a day up, rather than rounding up every fraction, as in the example above.
• The second half of the year for someone leaving after the waiting period is met: full entitlement, not a prorated figure.
• Additional leave for severely disabled employees under § 208 of Book IX of the Social Code (SGB IX), on top of the statutory minimum.
• A higher statutory minimum for young workers depending on their age at the start of the year, under § 19 of the Youth Employment Protection Act (JArbSchG).
• Vacation granted by contract or collective agreement beyond the 24 working days, which the statute itself does not regulate.
• Leave from an earlier employment in the same year, credited under § 6 BUrlG.

When Excel is enough for vacation entitlement

• For a plain annual entitlement with a fixed weekly pattern and no start or end date within the year: a single formula is enough.
• For a one-off calculation with some knowledge of the case distinctions: the conditional rounding formula above can also work out partial entitlement by hand.
• For ongoing calculations across several employees with different start and end dates: a calculator that checks the three cases and the rounding itself removes a source of error from an increasingly complex spreadsheet.
• If the Federal Vacation Act itself changes, which has happened rarely over the past decades, a homemade sheet has to be updated by hand, while a calculator behind an API follows the current version automatically.

The formulas in an English-language Excel

' B2: working days per week, for example 5
' B3: annual entitlement under § 3 BUrlG, converted using the BAG formula
=24*B2/6

' B4: full months of employment up to the date in question (found by hand or with a date formula)
' B5: fraction under § 5(1) BUrlG, one-twelfth per full month
=B3*B4/12

' This is how most homemade sheets build the rounding, and round up too often as a result:
=ROUNDUP(B5,0)

' Correct under § 5(2) BUrlG: round up only from half a day, smaller fractions stay as they are
=IF(MOD(B5,1)>=0.5,ROUNDUP(B5,0),ROUND(B5,2))

' End of the waiting period under § 4 BUrlG, needed to tell cases a, b and c apart
' B1: start date
=EDATE(B1,6)-1

The same case through the API

$ curl "https://api.quellenkontor.dev/v1/hr/urlaubsanspruch?arbeitstage_pro_woche=5&eintritt=2027-11-01" \
    -H "Authorization: Bearer $QK_KEY"

# Excerpt of the response
{
  "arbeitstage_pro_woche": 5,
  "anspruch_tage_jahr": 20,
  "teilurlaub": true,
  "teilurlaub_fall": "a",
  "volle_monate": 2,
  "anspruch_ungerundet": 3.33,
  "anspruch_tage_im_jahr": 3.33
}

The formula across many employees

For a single calculation, a wrong rounding result or a missed case in the second half of the year can still be checked by hand if needed. Once the same formula gets copied down for twenty or a hundred employees with different start and end dates, a single wrong case in the sheet barely stands out anymore, yet it affects every row it touches in the same way.

Testing a formula against a few known values before a sheet goes into real use helps catch this: a five-day week with no start or end date must come out to exactly 20 days, and an end date well into the second half of the year after long service must still give the full entitlement. If a formula already gets these simple cases wrong, it is worth finding the error before the harder partial-entitlement cases are added on top.

The calculator behind the API

The vacation entitlement calculator handles the case distinction and the rounding under § 5(2) BUrlG directly: with arbeitstage_pro_woche (working days per week) and optionally eintritt and austritt (start and end date), a call returns teilurlaub_fall (which case applies), volle_monate (full months) and both the rounded and the unrounded value. Every field and its type are listed in the vacation entitlement calculator reference.

Anyone checking partial entitlement for several new starters at once, for example during a larger hiring wave, finds a related calculation in the use case prorated pay for a mid-year start or end date, which follows the same principle using working days instead of vacation days.