Forum Discussion

Llamaface06's avatar
Llamaface06
Copper Contributor
Feb 17, 2026

Time sheet for hours worked

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 gra...
excel
Formulas and Functions
Llamaface06's avatar
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's avatar
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.

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Feb 20, 2026

    Hi NikolinoDE​ 

    Until we know how Llamaface06​  wants to round (will we...?) in borderline cases we waste our time IMHO. That said I like your FLOOR idea

    Cheers
    Lz.