Laytime calculation in Excel

Laytimetech · Updated 26 September 2026

You can calculate laytime in Excel, and most of the industry does. The structure is four columns of time, one column of judgement, and about six formulas. Below is a spreadsheet that works, with the arithmetic shown, followed by the five places it goes wrong — because every one of those five is a clause in the charter party that a spreadsheet cannot see.

What the spreadsheet has to do

Four steps, in this order:

1. Work out the time allowed — from the quantity and the rate, or from a stated period.
2. Work out when the clock starts — the notice of readiness, plus turn time.
3. Count the time used, period by period, excluding what the charter excludes.
4. Compare the two and price the difference as demurrage or despatch.

Steps 1, 3 and 4 are arithmetic and Excel is good at them. Step 2, and the exclusions inside step 3, are contract reading. That is the whole story of this page.

The structure that works

One row per period. Not one row per day — one row per event to event, because the charter excludes parts of days, not days.

From and To hold real date-times, not text. Format them dd/mm/yyyy hh:mm and let Excel store the underlying serial number — everything else depends on it.

Above the table, a small block of inputs:

The formulas

Hours in a period. Excel stores a date-time as days, so the difference is in days — multiply by 24. In C2:

=(B2-A2)*24

Counted hours. The percentage column is where SHEX, weather and half-days live. Full count is 1, excluded is 0, a half-rate period is 0.5. In E2:

=C2*D2

Time allowed, from the quantity and the rate:

=Quantity/Rate*24

30,000 ÷ 10,000 × 24 = 72 hours.

Laytime commences — notice plus turn time, remembering that turn time is in hours and Excel counts in days:

=NOR + TurnTime/24

20/04/2026 08:00 + 12 ÷ 24 = 20/04/2026 20:00.

Time used:

=SUM(E2:E100)

Time on demurrage, never negative:

=MAX(0, Used - Allowed)

Demurrage due:

=Demurrage/24 * MAX(0, Used - Allowed)

Despatch, if the ship finishes early — usually half the demurrage rate, and on most charters on working time saved rather than all time saved, which is a distinction worth checking in your own charter:

=Demurrage/2/24 * MAX(0, Allowed - Used)

That is a working laytime calculation. Build it once and it will serve you on a straightforward voyage.

A worked example

Illustrative figures, arithmetic shown in full.

Allowed: 30,000 ÷ 10,000 = 3.00 days = 72 hours

Commences: Monday 08:00 + 12 hours = Monday 20:00

Elapsed: Monday 20:00 → Friday 14:00 = 3 days 18 hours = 90 hours

Less excepted: 90 − 6 = 84 hours counted

On demurrage: 84 − 72 = 12 hours

Demurrage due: 12 ÷ 24 × 18,000 = USD 9,000

Move the notice by one hour and every line below it moves too.

Where the spreadsheet breaks

Five failures, and none of them is an arithmetic error. Every one is a clause the sheet cannot see.

1. The exclusion rules are wording, not switches

SHEX is not one rule. SHEX EIU excludes the day even if used — the ship worked and the time still does not count. SHEX UU excludes it unless used — she worked, so it does. A column of 0s and 1s cannot tell you which you are on; only the charter can, and the two produce different numbers from identical facts.

A weather working day is worse, because the test is counterfactual: it asks whether the weather would have permitted work on that cargo, not whether anyone was working. So a fine day nobody worked still counts, and a wet hour during which the ship was idle for her own reasons may not. NETWORKDAYS.INTL will give you working days; it will not give you weather working days, and nothing in Excel will. Both terms are set out in what is laytime.

2. Part-days, and the percentage nobody can reconstruct

Most charters exclude only the part of a day the weather actually interfered with. So column D fills up with 0.25 and 0.5 and 0.75 — and six months later, nobody can say why a particular period was counted at 40 per cent. The number is right and undefendable, which in a claim is the same as wrong.

3. Once on demurrage, always on demurrage

The single rule that moves the most money, and a spreadsheet does not know it.

Once the allowance is used up, the exceptions that protected the charterer generally stop applying. Demurrage runs continuously — nights, weekends, holidays — until completion. But the exclusion formula in column D carries on excluding, because that is what you told it to do. The sheet keeps deducting weekends after the ship is already on demurrage, and the claim comes back short.

Fixing it properly means the percentage in column D has to change behaviour part-way down the table, at the exact row where the running total crosses the allowance. That is possible in Excel and it is where home-made sheets most often go wrong.

It is also visible on a worked laytime statement example, where a declared public holiday is excepted on one account and counts in full on the other — because on that one the ship had already gone on demurrage.

4. The version nobody owns

The laytime spreadsheet in most offices was built once by somebody who has since left, copied between colleagues, and patched at midnight when a statement of facts arrived in a shape it was not designed for. Nobody knows which copy is current. The formulas have been overtyped with hard numbers in at least three cells, and no one can tell which.

This is not a joke about spreadsheets. It is the reason two people at the same desk produce two different figures from the same papers.

5. It cannot show its working

When the claim is disputed — and it will be, six months later, by somebody being paid to dispute it — the question is not what is your number. It is which clause made that hour count.

A spreadsheet has a cell with 0.5 in it. It does not have the rider that put it there. Rebuilding that reasoning from the charter party, the recap, the notices and a statement of facts the agent typed in a hurry is the part that takes an afternoon, and it is the part that decides whether the claim is paid.

What showing the working looks like: this laytime statement example for a four-port voyage prints the clause against every hour, and prices the same shore crane breakdown two different ways under two different contracts.

What to do about it

If you keep the spreadsheet — and plenty of desks reasonably will — three habits are worth more than any formula:

• Put the clause in column F on the day you build the row, not when the claim is challenged. Two words beside the percentage — rain, cl. 24 — is the whole difference between a defendable claim and an argument.
• Mark the row where demurrage begins and check that nothing is being excluded below it.
• Keep the statement of facts, the notice and the recap with the file, not in the email thread they arrived in.

Common questions

Is there a free laytime calculation Excel template to download?

Plenty circulate, and most of them handle a straightforward voyage. The structure above is the same one they use. What none of them can do is read your charter party — the percentage column is always someone's judgement, and a downloaded template makes that judgement invisible rather than absent.

Can Excel handle SHEX and weather working days?

It can hold the result of the decision, not make it. NETWORKDAYS.INTL excludes weekends and listed holidays; a weather working day is a question about what the weather would have permitted for that cargo, which no formula answers.

Why do two people get different laytime figures from the same papers?

Almost never arithmetic. It is which hours counted — and behind that, which clause each person applied, and whether either of them noticed that the exceptions stop at the moment the ship goes on demurrage.

How do I calculate demurrage in Excel?

demurrage rate ÷ 24 × hours over the allowance. Wrap it in MAX(0, …) so despatch does not show as negative demurrage.