Can you calculate Hospitality Award penalty rates in Excel? The formulas and their limits

The short version: yes, a spreadsheet can price ordinary hours under the Hospitality Award (MA000009) correctly, and this piece gives you the rates sheet, the roster sheet and the lookup formulas to do it, checked against the Fair Work Ombudsman pay guide that applies from 1 July 2026. What a spreadsheet cannot do is apply the rules that sit around the hours: the 10-hour break, classification by duties, casual overtime after 38 hours, the roster cycle for permanents, junior percentages and the 1 July repricing. Each of those is a cost the sheet will not show you. This is general information, not legal or financial advice. The Fair Work Ombudsman and the award text are the source of truth, and the business is responsible for verifying what it pays.

Start with one cell. Sarah is a Level 2 casual, a food and beverage attendant grade 2, rostered on the main bar this Saturday from 5pm to 11pm. From the first full pay period on or after 1 July 2026 her minimum hourly rate is $27.08, and a casual working ordinary hours on a Saturday is paid 150% of it. The cell is =PRODUCT(27.08,1.5), it shows $40.62, and six hours of it is $243.72. That is the whole of the hard part of pricing a Saturday, and a spreadsheet does it without complaint.

So the answer to the question in the title is yes. You can build a sheet that prices every ordinary hour under MA000009 to the cent, and most venues that roster in Excel already have one that mostly works. The rest of this piece gives you the rates, the three sheets and the formulas, carries Sarah through a fortnight, and then goes through the six rules the formula cannot follow. The formulas are the easy part. The rules between the cells are where the money goes.

Everything below works the same in Excel and Google Sheets. XLOOKUP, MOD, TEXT and COUNTIF behave identically in both, and so does the gap: neither of them knows what an award is. Moving the sheet to Google does not fix a single thing in the second half of this piece.

The rate table you need

Seven adult levels, one minimum hourly rate each, and a set of percentages that sit on top. The percentages are of the minimum hourly rate for the employee's level, not of the loaded casual rate, and they do not stack: clause 29.3 pays only the highest penalty that applies to an hour.

Last verified: October 2026, against the Fair Work Ombudsman pay guide for the Hospitality Industry (General) Award, published 24 June 2026 and applying from the first full pay period on or after 1 July 2026, and against clause 18.1 (Table 3) and clause 29.2 (Table 14) of MA000009. Our Hospitality Award reference page carries the full set, with allowances and minimum shifts.

Full-time and part-time staff are paid 125% on Saturday, 150% on Sunday and 225% on a public holiday. Monday to Friday evenings are a flat dollar amount rather than a percentage: an extra $2.95 for each hour or part of an hour between 7pm and midnight, and $4.42 between midnight and 7am, for permanents and casuals alike. The two figures are 10% and 15% of the standard hourly rate, which the award defines as the Level 4 minimum ($29.45), so they are the same at every level, and they move every July too.

Building it: a rates sheet, a roster sheet and one lookup

Three parts. A Rates sheet that holds every number that can change. A Roster sheet with one row per shift. And a lookup in each row that reads the first from the second. The discipline that matters is that no rate is ever typed into the roster. If 27.08 appears anywhere outside the Rates sheet, that cell is the one that will still say 27.08 in August 2027.

On the Rates sheet, columns A and B hold the level and its minimum hourly rate, one row per level (A2:B8). Columns D to F hold the day type and its multiplier for casuals and for permanents: Mon to Fri 1.25 and 1.00, Sat 1.50 and 1.25, Sun 1.75 and 1.50, PH 2.50 and 2.25 (D2:F9). B10 holds the evening loading, 2.95, and B11 the night loading, 4.42. Column H lists your state's public holidays as dates. On the Roster sheet, each row is a shift: name in A, level in B, Casual or Permanent in C, date in D, start in E, finish in F, and the calculated columns from G across. Multiplication is written as PRODUCT() below so the formulas copy cleanly; the asterisk does the same job.

Sarah's Saturday is row 2: Level 2, Casual, Sat, 17:00 to 23:00. H is 6, I is 27.08, J is 1.5, K and L are 0, and M is $243.72. The holiday check runs before the weekday check, so a public holiday that lands on a Saturday prices at 2.5 rather than 1.5, which is the Anzac Day line most sheets are missing.

Splitting a shift at 7pm and at midnight

The two awkward columns are K and L, and they are awkward because the award changes the price part-way through a shift. Thursday 5pm to 11pm is two ordinary hours at $33.85 and four evening hours at $33.85 plus $2.95. The formula does that by treating times as fractions of a day: 7pm is 19/24, midnight is 1, and the evening hours are whatever sits between the later of start and 7pm and the earlier of finish and midnight. A finish after midnight has a smaller number than the start, which is the IF(F2<E2) branch; it pushes the evening window out to midnight and hands the rest to column L.

One thing the day-type column cannot do is change mid-shift. A Friday 6pm to 2am close is Friday until midnight and Saturday after it, and the two hours after midnight are Saturday hours at 150%, not Friday night hours at 125% plus $4.42, because clause 29.3 pays the higher of the two. If you close after midnight on Fridays and Sundays you need a second row for the part after midnight, or a column that splits it. That is the first sign of the pattern this piece is about: every rule the award adds is another column, and the columns are the easy ones.

A worked fortnight: Sarah's five shifts

Sarah, Level 2 casual, five shifts in a fortnight, none over six hours so there is no unpaid meal break to take out. The sheet above prices all five correctly.

Thirty hours, $1,352.26, every line reconciling to the pay guide. If this is what your sheet produces, it is not broken. It is doing the job a sheet can do, and the next six headings are the job it cannot.

A spreadsheet can price the hours. It cannot see the rule between them.

The six rules the formula cannot follow

Each one below is a rule in MA000009 that depends on something other than the hours in the row: the row before it, the person's duties, the week's total, the roster cycle, a birthday, or the calendar. The sheet has none of that in scope, so it prices the row and moves on.

1. The 10-hour break between shifts (clause 15.5(e))

A full-time or part-time employee must have a minimum break of 10 hours between finishing ordinary hours on one day and starting them on the next, and 8 hours on a changeover of rosters. A close at 1am and an open at 9am is an eight-hour gap, and in the sheet it is two adjacent cells that look like every other pair. No formula in the roster above reads the previous row, and adding one means sorting every shift by person and time first, which is the one thing a roster laid out by day is not. What it costs: the award does not print a price for a short break, so the cost is the breach itself. The roster is the record, the record shows a permanent rostered outside the award, and the fix arrives on payday as a conversation rather than a cell. Our piece on the 10-hour break when you work across two venues covers the carve-outs, including the fact that clause 15.5 is written for permanents, not casuals.

2. Classification by duties, not by title

Column B says Level 2 because Jack is a bartender and that is what the column has always said. The award's classifications are about duties and supervision, not titles, and in April Jack started running the bar unsupervised on Sundays, which is the work of a food and beverage attendant grade 3, Level 3. Nothing in the sheet knows that. What it costs: Level 3 is $34.96 an hour as a casual against $33.85, and $41.96 on a Saturday against $40.62. At 20 hours a week, 14 of them on weekdays and 6 on Saturday, that is $23.58 a week, or $613.08 over the 26 weeks until someone next looks at column B. Our guide to the Hospitality Award levels walks through what moves a person between them.

3. Casual overtime is priced off the minimum rate, not the loaded one

A casual is on overtime after 12 hours in a day or shift, or beyond 38 hours a week averaged over the roster cycle (clause 11.2), and the overtime rate is a percentage of the ordinary hourly rate, which for a casual is the $27.08 minimum, not the $33.85 loaded rate. Monday to Friday that is 150% for the first two hours and 200% after them: $40.62, then $54.16. The loading is not added on top, so hour 39 on a Thursday pays the same $40.62 as an ordinary hour on a Saturday, which is the kind of coincidence that hides an error for years. Your multiplier column only knows day types. When Sarah covers a gap and reaches 42 hours in a week, the sheet pays $33.85 for every one of them. What it costs: hours 39 and 40 are short by $6.77 each and hours 41 and 42 by $20.31 each, $54.16 for the week, and the error runs the other way if you multiply the loaded rate by 1.5 instead. Casuals do get overtime prices one Friday gap three ways.

4. The 38-hour permanent threshold runs across a roster cycle

A full-time employee works an average of 38 ordinary hours a week, and clause 15.1 lets that average be struck over a cycle: 76 hours over two weeks, 152 over four, with minimum days off attached. Overtime starts when the ordinary hours for the arrangement run out, at 150% for the first two weekday hours and 200% after, and 200% at the weekend. The sheet sees one week at a time and does not know which arrangement the person is on, so a 42-hour week looks the same whether it is four hours of overtime or the heavy half of a 76-hour fortnight. What it costs, one way: a Level 2 permanent paid $27.08 flat for hours 39 to 42 of a weekly arrangement is short $27.08 on the first two and $54.16 on the next two, $81.24 for the week. The other way, four hours priced as overtime that were ordinary hours on a two-week arrangement, is $81.24 handed out.

5. Junior percentages, and the liquor exception

Table 5 of the award pays juniors a percentage of the adult minimum for their level: 50% under 17, 60% at 17, 70% at 18, 85% at 19, and the full rate at 20. Two things the sheet cannot see. The first is a birthday: a 19-year-old on $28.78 as a Level 2 casual becomes $33.85 from the day they turn 20, mid-fortnight, and the row does not know. The second is clause 13.5, which pays a junior liquor service employee as an adult regardless of age. What it costs: an 18-year-old on the bar priced at 70% is $23.70 an hour on a weekday and $28.44 on a Saturday, and the adult rate the award requires is $33.85 and $40.62. That is $12.18 an hour short on a Saturday, $73.08 on one six-hour shift, every week, for the person pouring the drinks.

6. The 1 July repricing, every year

The Fair Work Commission's annual wage review lands in June and the new minimums apply from the first full pay period on or after 1 July. The 2026 pay guide was published on 24 June. A rates sheet is right on the day you build it and wrong on the first full pay period after the next 1 July, unless someone remembers, and the sheet has no way of telling you it is out of date; $27.08 looks as plausible in July 2027 as it does today. The size of the 2026 rise, and why the bottom levels got more than the headline figure, is in our 1 July post. What it costs: the whole of the increase on every hour until the cell is edited, and the multipliers scale it. A Saturday hour carries one and a half times the base error, a Sunday hour 1.75 times, a public holiday hour two and a half. Everything on the Rates sheet moves at once, including the $2.95 and $4.42, because they are percentages of the Level 4 rate. Each July, put the pay guide's published date next to the date on your Rates sheet. If the sheet is older, every total under it is too.

What this means for a 20+ staff venue

At one room and one editor, you can run the sheet above and check the six rules by hand on every change, and plenty of venues do. At 20-plus staff with a kitchen, a bar and two people editing, the checking is what stops happening. Our complete guide to moving a 20+ staff venue off Excel prices the sheet for a 22-staff pub at about $13,700 a year in manager time, re-keying and one recurring error, and lists the 12 signs that a venue has outgrown it. Sign eight is this piece in one line: the roster costed by formula, and the formula is wrong.

The recurring error in that table is worth restating here because it is the simplest version of what a wrong multiplier costs. One Level 2 casual, one eight-hour Saturday a fortnight paid at the weekday casual rate of $33.85 instead of $40.62, is $6.77 an hour, $54.16 a shift and $1,408.16 a year. That is a single wrong number on a single person, and it reconciles to the pay guide above. Most sheets at that size are carrying more than one.

Where Shiftly fits

We built Shiftly's award interpretation to hold the part of this piece the sheet cannot. The award level sits on the person, not in a cell, so Jack's move to Level 3 reprices every future shift once, and the junior percentage follows the date of birth. The Hospitality Award is a supported award, and the engine applies the Saturday, Sunday and public holiday rates, the 7pm and midnight loadings, the casual loading and the overtime thresholds to every shift as you drop it on the roster, with a running estimate for the fortnight before you publish. The 10-hour break is still yours to watch: Shiftly prices the hours, and it does not police the gap between them. The core platform is free at every headcount, with no per-employee fees.

Shiftly is a calculation tool and a staffing network, not a payroll provider. Every estimate shows the hours, penalties, loadings and overtime behind it so you can check it against the pay guide, and the business stays responsible for verifying what it pays; the Fair Work Ombudsman remains the source of truth. The other half of the product is the roster filling itself: once the on-demand network is live in your area, starting with Sydney, an open shift can go out to local hospitality workers from inside the roster. It is coming soon, and the waitlist is open. Get started with Shiftly

Frequently asked questions

Can you calculate Hospitality Award penalty rates in Excel?

Yes, for ordinary hours. A rates sheet with the minimum hourly rate per level, a multiplier table (casual 125%, Saturday 150%, Sunday 175%, public holiday 250% for casuals) and an XLOOKUP in each roster row prices a shift to the cent, including the $2.95 evening and $4.42 night loadings if you split the hours at 7pm and midnight. What the sheet cannot do is apply the rules that depend on other rows or on the person: the 10-hour break, classification by duties, overtime thresholds, junior rates and the 1 July change.

What is the Saturday rate for a casual under the Hospitality Award in 2026?

From the first full pay period on or after 1 July 2026, a casual working ordinary hours on a Saturday is paid 150% of the minimum hourly rate for their level, which includes the casual loading. For a Level 2 casual on $27.08 that is $40.62 an hour; Level 1 is $39.66 and Level 3 is $41.96. Full-time and part-time employees are paid 125% on Saturday. The figures are in the Fair Work Ombudsman pay guide published 24 June 2026.

Does Google Sheets handle award rules better than Excel?

No. XLOOKUP, MOD, TEXT and COUNTIF behave the same in both, so the formulas in this piece work unchanged, and so does their limit. Neither application knows what a roster cycle, a classification or a public holiday is unless you build it, and neither can read the row above to find an eight-hour gap. Sheets makes sharing one version easier, which fixes a different problem.

Why is casual overtime cheaper per hour than I expected?

Because the overtime percentages in clause 28 are applied to the ordinary hourly rate, which for a casual is the minimum rate without the 25% loading. A Level 2 casual's first two weekday overtime hours are 150% of $27.08, or $40.62, and 200% after that, $54.16; the loading does not sit on top. A sheet that multiplies the loaded $33.85 by 1.5 pays $50.78 and overpays. One that keeps paying $33.85 past 38 hours underpays by $6.77 an hour, then $20.31.

The bottom line

The formulas are the easy part, and this piece has given them away: three sheets, seven columns, and every ordinary hour under MA000009 priced to the cent. The rules between the cells are why venues move. A 10-hour break, a classification, a 38-hour threshold, a roster cycle, a birthday and a 1 July are all invisible to a row that only knows its own start, finish and day. If your venue is at the size where nobody checks those six by hand any more, the fortnight plan in the pillar is the way out, and award interpretation is where the rates live once you take them out of the cell.