Get decimal hours by six-minute bands

7 hours 33 minutes is 7.6 in six-minute bands and 7.55 in hundredths, never 7.33.

What you'll make

In Excel: 7 hours 33 minutes, six-minute bands becomes 7.6 hours.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1C8Reads the conversion profile for this row."Published six-minute bands"
2A8+CEILING.MATH(B8/6,1)/10Bands: each started 6 minutes adds 0.1 hour.7.6
3"Hundredths",ROUND(…)Hundredths: minutes divided by 60, to 2 decimals.Not used for row 8
4SWITCH( … )Uses the part that matches the profile.7.6

Read it left to right. Any other profile says Review minute-conversion profile.

=SWITCH(C8,"Published six-minute bands",A8+CEILING.MATH(B8/6,1)/10,"Hundredths",ROUND(A8+B8/60,2),"Review minute-conversion profile")

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own hours. Paste the whole hours and the leftover minutes (0 to 59) into the green columns of the Template tab. Add the profile, either Published six-minute bands or Hundredths. The decimal hours fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Put only 0 to 59 in the minutes column. Take the profile from your source, not from the result you want.

Microsoft's help: CEILING.MATH function