Straight into the service manual page
WrenchQuarry
More

MROUND formula rounds labor hours to the nearest 0.5

WrenchQuarry Editorial Team · Published · 12 min read · How these guides are written

Use Microsoft's MROUND function with 0.5 as the multiple: =MROUND(raw cell, 0.5). Microsoft's ROUND page gives the rule directly: "To round a number to a specific multiple (for example, to round to the nearest 0.5), use the MROUND function." Write that function, the 0.5 step, and the field into one sentence on every job record.

Net takeaways

  • Round technician labor hours to the nearest 0.5 with Microsoft's MROUND function, using 0.5 as the multiple, rather than plain ROUND.
  • Plain ROUND with num_digits of 0 gives whole hours, not half-hour steps, so it does not fit this rule.
  • Set up separate raw and billed columns, keep the formula only in the billed column, and limit who can edit it.
  • Write the rule as one sentence naming the function, the step, and the field, then apply it to every ticket the same way.
  • Do not stretch a labor hours rule to cover parts, materials, or travel time.

Why labor hours need one rounding rule

To round technician labor hours to the nearest 0.5, use Microsoft's MROUND function with 0.5 as the multiple, then write that rule on every job record. One written rule keeps billed hours consistent from ticket to ticket.

Labor hours need that rule because the job record is what the office reads when it bills a customer, and it is what an owner reads when reviewing how a job went. If one person rounds a technician's time up and another rounds it down, two similar jobs can show different hours. Nobody reading the record can then tell whether the difference came from the work or from the entry.

The rule does not need to be complicated. This post describes one approach: round to the nearest 0.5 with MROUND, record the rule on the job, and apply it the same way to every ticket. The 0.5 step comes from the example on Microsoft's ROUND function page, so both the function and the step have a source.

Whether a half-hour step fits your shop is your decision. It should match the billing terms you give customers, so check those terms before you change anything on the sheet.

A rule only helps if the people who touch the record know it exists. Dispatchers who enter time, technicians who report it, and the owner who signs off on invoices should all read the same written procedure. A rule that lives in one person's head is hard to check. A written rule can be pointed to when someone asks why the hours look the way they do, and it can be corrected in one place when it changes.

This post covers the function, the columns, the written rule, and the checks. It does not cover parts, materials, or travel time. The sources here say nothing about those, so do not stretch a labor rule to cover them.

Use MROUND for a half-hour step

Microsoft's ROUND function page names the MROUND function for rounding to a specific multiple, and its example is the nearest 0.5. The page says: "To round a number to a specific multiple (for example, to round to the nearest 0.5), use the MROUND function." That sentence is the source for the function and the step used in this post.

Write the function name exactly as MROUND in the procedure and in any formula you share. A basic version looks like =MROUND(raw cell, 0.5), where "raw cell" is the address of the raw entry.

The quote tells you what MROUND is for. It does not say how the function handles a value that falls exactly halfway between two steps, so do not assume an answer. Before you rely on the formula, copy a few test values into a scratch copy of the sheet. Include one value that sits exactly halfway between two steps, and write down what comes back. That note becomes part of the procedure.

Why plain ROUND gives the wrong step

The ROUND function rounds a number to a set number of digits, controlled by the num_digits argument. The page describes that argument as "the number of digits to which you want to round the number argument." The page gives three cases for num_digits, shown in the table below.

Value of num_digitsWhat happensFits half-hour step
Greater than 0rounded to specified decimal placesNo
0rounded to the nearest integerNo
Less than 0rounded left of the decimal pointNo
Value of num_digitsWhat Microsoft's ROUND page says
Greater than 0number is rounded to the specified number of decimal places
0the number is rounded to the nearest integer
Less than 0the number is rounded to the left of the decimal point

The zero row is the trap. A num_digits of 0 rounds to the nearest integer, which gives whole hours, not the half-hour steps this rule uses. For a half-hour step, the page points to MROUND, as covered above.

The less-than-0 row describes rounding to the left of the decimal point. That is a different position from a half-hour step, so it does not apply to labor hours. If your sheet already rounds labor hours in some other cell, find that formula and either remove it or label it clearly. Two rounding formulas on one record make the hours hard to read.

Read the page itself before you quote any row in a procedure. This post reproduces only the lines it needs, and the page says more around them.

Set up raw and billed columns

Set up two columns for labor time: one for the raw entry and one for the rounded hours the formula returns. Put the raw entry first, because the rounded column depends on it. Label them plainly, such as "Raw labor entry" and "Billed labor hours," so the billing column is obvious to anyone reading the sheet.

Keep the formula only in the rounded column. Then add a third, small column for notes or an override reason beside the rounded hours. An exception then sits on the same row as the number it changed.

Clock times need one extra step. Convert clock times to a plain decimal hours column before rounding, and check that conversion the same way you check the rounding. Convert it to a plain decimal number of hours in its own column first, and check that conversion the same way you check the rounding. The NIST style guide shows one value written both as "0.15" and as "15 %," so pick one form for the sheet and keep to it. (NIST, SI style conventions)

The table below sets out who should handle each column.

ColumnWhat it holdsWho may edit it
Raw labor entryThe time as measured or typedDispatcher or technician
Decimal hours (if needed)A clock time converted to decimal hoursOne named person
Billed labor hoursThe MROUND result of the raw entryOwner or one named person
Notes or override reasonWhy a billed value differs from the ruleAnyone, with a reason written

Next, build a short test set on a separate tab. Include the main cases your shop sees, plus values that sit exactly halfway between two steps. Compare each rounded result against the rule. Keep the test rows apart from job data so they never mix with billing records.

Then check that the rounded column responds to the raw column. Change one raw value on the test tab, confirm that the rounded value updates, and put the test value back. If it does not change, the formula is not reading the column you think it is.

Last, decide who can edit the formula column. A column anyone can type over can drift from the rule. Limit edits to the owner or one named person, and record any change to the formula in the procedure.

Write the rule on the job record

Write the rule as one sentence that names the function, the step, and the field. For example: "Labor hours are rounded to the nearest 0.5 with MROUND, and the raw time is kept in its own field." Place it at the top of the dispatch procedure and in any notes area attached to jobs, so the rule travels with the record.

Then decide where the rounding happens. You can round at entry, so the stored hours are already on a half-hour step. Or you can round once, when the total is calculated for billing, and leave the raw entries alone. The second choice keeps the raw time available for review and puts the rounding in one place. I lean toward it, but the owner should make the call, because it decides what the customer's bill is based on.

Write down who may override the rule and how. An override should be an exception with a reason in the notes field, not a silent change to the number. Overrides should be rare, and an owner reading the record later should be able to see which jobs were exceptions and why.

State the scope inside the rule as well: labor hours only. A rule that names its field is easy to apply, and easy to spot when someone applies it to the wrong thing.

If a customer sees labor hours on an invoice, the written rule answers the question of why the hours look the way they do. Share it in plain words rather than as a formula. The sentence you wrote for the record works for this too.

Rounding multi-visit jobs

Decide whether the rule rounds each visit or the job total, and write that choice into the procedure. The two can give different totals. Rounding each visit and then adding the results is not always the same as adding the visits first and rounding once. Neither choice is wrong on its own, but mixing them across tickets makes records hard to compare.

If a job is reopened or returns for a second visit, apply the same choice to the new time. Do not round an earlier visit a second time. Rounding a value that is already rounded is a second step the record may not show.

Pick the choice that matches how you bill. Then walk through one row from your test tab for each case, so the next person can follow it without guessing. Write the choice as a single line beside the rule so it is easy to find.

If you change the choice later, date the change the same way you date any other change to the rule. That keeps jobs recorded under each version easy to tell apart.

Apply the same rule to every ticket

Use one column, one formula, and one point in the workflow. When a second column or second formula appears, two versions of the hours exist, and a reader has to work out which one was billed. Keep the formula in the rounded column only, and limit who can type over it.

Treat every technician and every job type the same way. The rule should not change because a job is short or long, or because one technician reports time differently from another. If you think a job type needs a different rule, change the written rule for everyone and record the change. Do not let one dispatcher run a private version.

When the procedure changes, write the date it takes effect next to the rule. Apply the new version to jobs opened on or after that date, unless the owner decides otherwise, and leave earlier jobs as they were recorded.

When someone new starts entering time, show them the written rule and walk through one row from the test tab. Ask them to say back what happens to the raw time and what happens to the rounded hours. A short check at the start is easier than fixing records later.

When a technician or customer questions an hour on a bill, look at the raw entry first, then the rounded value, then the written rule. If the rule was applied as written, the answer is in the record. If it was not, the record shows where it went wrong, and that is the place to fix it.

Check closed jobs against the rule

Check closed jobs by comparing the raw time with the rounded hours, and confirm that each rounded value sits on a half-hour step. Sample closed jobs at regular intervals rather than all at once, and start with any that carry overrides. The check is faster when the raw and rounded columns sit side by side.

If a rounded value is not on a half-hour step, the formula was probably overwritten, pasted as a plain value, or applied to the wrong column. Find out which before you change anything, and write down what you found. A fix with no note hides the cause, and the same problem can come back.

Review the override list the same way. An override with no reason is a gap in the record, and the owner should see it. Overrides that repeat for the same reason suggest the written rule may need a change. Make that change in the procedure, not one job at a time.

Keep a short log of what you checked, when, and what you found. The log shows the check happened, and an owner can read it without reopening every job.

On any one job, a rounding rule looks small. Across the office, it decides whether two similar jobs can be compared at all. Put the function, the step, and the field into one sentence, then check the record against that sentence.

Questions readers ask

Which function should I use to round labor hours to the nearest 0.5?

Use Microsoft's MROUND function with 0.5 as the multiple. Microsoft's ROUND function page says: "To round a number to a specific multiple (for example, to round to the nearest 0.5), use the MROUND function." A basic formula looks like =MROUND(raw cell, 0.5).

Why doesn't plain ROUND work for a half-hour step?

ROUND rounds to a number of digits, not to a multiple. A num_digits of 0 rounds to the nearest integer, giving whole hours instead of half-hour steps, and other num_digits values round to decimal places or left of the decimal point. None of those cases produce a 0.5 step.

How should I set up the columns on the sheet?

Use a raw entry column first, then a billed column holding the MROUND result, and a small notes column for override reasons. Keep the formula only in the billed column and label the columns plainly, such as "Raw labor entry" and "Billed labor hours."

What should I do with clock times before rounding them?

Convert clock times to a plain decimal number of hours in their own column before rounding. Check that conversion the same way you check the rounding, and pick one form for the sheet, such as 0.15 or 15 %, and keep to it.

Where should the written rounding rule live?

Write it as one sentence naming the function, the step, and the field, then place it at the top of the dispatch procedure and in any notes area attached to jobs. Share it with dispatchers, technicians, and the owner so everyone reads the same procedure.

Should I round each visit or the total for multi-visit jobs?

Decide once and write the choice into the procedure. Rounding each visit and adding the results is not always the same as adding the visits first and rounding once, so mixing them across tickets makes records hard to compare.

Sources

More field guides