I am needing to make a sheet that shows clocking in for work - clocking out for lunch - clocking in from lunch - clocking out for the day and then the total of those hours worked per day and then grand total for the week. For our pay system we do things in 6 minute increments. I found a sample sheet through Excel, but the formula we have still isnt just right. Here is what I have and the formula for the hours worked is what I am needing help on. Any help is very much appreciated!

It may be clear to you what "we do things in 6 minute increments" means in this context. I know what the phrase means--and am assuming it is convenient because it refers to tenths of an hour--but you don't make clear how you're using it (or hoping to use it) in tracking time. Here are the possibilities that occur to me. Does it mean you:round each entry to the nearest tenth of an hourround each segment of clocked in to clocked out to the nearest tenth of an houronly round the final total "Hours Worked" to the nearest tenth of an hoursomething else?

HiYour example gives 7.65 hours and you seem to want 7.6==> That could be a round Down to the 1st digitwhat do you expect for the other above examples?

Could I just give you a few words of encouragement, based on (believe it or not) 50 years of working with arrays of data, most of that time with spreadsheets as the primary tool.You don't ever need to "hope" that there's a formula to get from A to B. Quite seriously. A rule of thumb that I developed many years ago, first for myself, then for others who were still learning: IF you find yourself thinking along the lines "Excel should be able to ________" THEN you can be confident that the programmers have indeed made it possible. The challenge is to find the function or functions that make it possible.Related to that rule of thumb is the reality that you do need to find a comprehensive and clear way to describe the situation, thinking of these three stages: input, process, output. Rather than kicking yourself for "not sure how to word everything" think of it as describing, especially describing fully the process aspect. And focus on describing in plain English (no need to try to translate into "computerese" or "Excel functions"; that comes later).Usually, once you've described the input, process, and output--your original description here had the first and last parts of that trio; it just left vague the process itself--it becomes relatively easy to find an Excel solution because the names of the functions themselves are close enough to English descriptions of what they do that you can go down a comprehensive list of functions and start playing with them to see if they get you from A to B.Let me also underscore my use of the word "play" in that last sentence. Give yourself permission to go through trial and error. Play. You won't break anything and you will learn.

Here's a small attempt, maybe this will help you. That calculates total hours worked with 6-minute increments (rounded to the nearest tenth of an hour). =FLOOR(( (Clock_Out - Clock_In) - (Lunch_Out - Lunch_In) ) * 24, 0.1) Example Breakdown:Clock_In: 8:00 AM (0.3333 in Excel)Lunch_Out: 12:00 PM (0.5)Lunch_In: 1:21 PM (0.55625)Clock_Out: 5:00 PM (0.70833)Calculation:=FLOOR(( (0.70833 - 0.3333) - (0.55625 - 0.5) ) * 24, 0.1)=FLOOR(7.65, 0.1)= 7.6Ensure cells are formatted as [h]:mm to display time correctly.

Sorry - wasn't sure how to word everything. So we round the total hours worked for each day to the nearest tenth of an hour. Here is an example -Clocked in 8:00AMLunch out 12:00PMLunch in 1:21PMClocked out 5:00PMTotals to 7.6I am hoping there is a formula for excel that will give me that exact answer. Thank you :)

Time sheet for hours worked | Microsoft Community Hub

12 Replies

Lorenzo
Silver Contributor
Feb 19, 2026
Llamaface06
On Feb 18th someone replied on the following post:
The Show More link obviously bugs and no way to see that reply. If it was you responding to our question re. Rounding please re-post with a reply on this post
raqim88
Copper Contributor
Feb 17, 2026
You can just calculate morning time plus afternoon time, then round it. For example, total hours = (lunch out − clock in) + (clock out − lunch in). Then round that result to 6-minute increments using MROUND. After that, sum your daily totals for the week. Make sure the cells are formatted as time so it calculates correctly. This setup usually works for simple timesheets.
mathetes
Gold Contributor
Feb 17, 2026
It may be clear to you what "we do things in 6 minute increments" means in this context. I know what the phrase means--and am assuming it is convenient because it refers to tenths of an hour--but you don't make clear how you're using it (or hoping to use it) in tracking time. Here are the possibilities that occur to me. Does it mean you:
round each entry to the nearest tenth of an hour
round each segment of clocked in to clocked out to the nearest tenth of an hour
only round the final total "Hours Worked" to the nearest tenth of an hour
something else?
- raqim88
  Copper Contributor
  Feb 17, 2026
  You can just calculate morning time plus afternoon time, then round it. For example, total hours = (lunch out − clock in) + (clock out − lunch in). Then round that result to 6-minute increments using MROUND. After that, sum your daily totals for the week. Make sure the cells are formatted as time so it calculates correctly. This setup usually works for simple timesheets.
- Llamaface06
  Copper Contributor
  Feb 17, 2026
  Sorry - wasn't sure how to word everything. So we round the total hours worked for each day to the nearest tenth of an hour. Here is an example -
  Clocked in 8:00AM
  Lunch out 12:00PM
  Lunch in 1:21PM
  Clocked out 5:00PM
  Totals to 7.6
  I am hoping there is a formula for excel that will give me that exact answer. Thank you :)
  - NikolinoDE
    Platinum Contributor
    Feb 20, 2026
    Here's a small attempt, maybe this will help you.
    
    That calculates total hours worked with 6-minute increments (rounded to the nearest tenth of an hour).
    
    =FLOOR(( (Clock_Out - Clock_In) - (Lunch_Out - Lunch_In) ) * 24, 0.1)
    
    Example Breakdown:
    Clock_In: 8:00 AM (0.3333 in Excel)
    Lunch_Out: 12:00 PM (0.5)
    Lunch_In: 1:21 PM (0.55625)
    Clock_Out: 5:00 PM (0.70833)
    Calculation:
    =FLOOR(( (0.70833 - 0.3333) - (0.55625 - 0.5) ) * 24, 0.1)
    =FLOOR(7.65, 0.1)
    = 7.6
    Ensure cells are formatted as [h]:mm to display time correctly.

Forum Discussion

Time sheet for hours worked