 # Calculate Hours Worked Based On 2 Start/End Times

• Hi all,

It's been several years sine I had to look at calculating amount of time worked.
Can you please look at this old spreadsheet of mine and verify that the formula is correct?

It appears to be ok to me, but I don't want any errors when it comes to paying my employees

Formula:
=ROUND(HOUR(\$J3-\$G3)+(0.01*MINUTE(\$J3-\$G3)*1.67),1)-ROUND(HOUR(\$I3-\$H3)+(0.01*MINUTE(\$I3-\$H3)*1.67),1)

Thanks
Regards,
marc

## Files

• Re: Calculate Time Worked

In K3 and down, [COLOR="Blue"]=MIN("8:00", MOD(H3 - G3, 1) + MOD(J3 - I3, 1) )[/COLOR]

In L3 and down, [COLOR="blue"]=MAX(0, MOD(H3 - G3, 1) + MOD(J3 - I3, 1) ) - "8:00"[/COLOR]

... and format both as [COLOR="blue"]h:mm[/COLOR]

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

• Re: Calculate Time Worked

I use this since I need decimal hours versus exact minute differences:

=(((H3-G3)*1440)/60)+(((J3-I3)*1440)/60)

• Re: Calculate Time Worked

You could avoid a potential source of input error by eliminating the Day column, and formatting the Date column as [COLOR="Blue"]ddd mm/dd/yyyy[/COLOR]

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

• Re: Calculate Hours Worked Based On 2 Start/End Times

Thanks shg,

The formula returned the exact same result of 8hrs worked as did my formula