Calculating overtime for employees

**nickmessick1** · 02-08-2013, 06:03 PM

Hi,

I am having trouble with a formula to calculate overtime. I have attached an example below. Each employee is scheduled a certain amount of hours (Column D) and when they work past those hours, each additional hour is overtime. I have put in the hours for the start of work and end of work. I also input the dates because sometimes the employees work overnight into a different date. Every six minutes counts as 0.1 hours in our timekeeping. I think I can round up for the hours if they work 15 minutes extra. Thanks for your help.

overtime question.xlsx

**Melvinrobb** · 02-08-2013, 06:32 PM

Would the attached work?

**nickmessick1** · 02-08-2013, 06:40 PM

I like it, however for me, it would be better to have a whole number as the result. So if the person worked 1 hour and 15 minutes, it would show as 1.25. That's where I have trouble. I appreciate the help.

**Melvinrobb** · 02-08-2013, 06:44 PM

change the formula in I3 to this:

Formula:

Please Login or Register  to view this content.

Then change the format back to "number" and you should have what you need.

**nickmessick1** · 02-08-2013, 06:49 PM

That worked great. You really helped me out. Hopefully others can use that too. Thanks

**Melvinrobb** · 02-08-2013, 06:51 PM

Here is the rule when converting between "number" values and "time" values:

A "Time" value in excel is treated as a decimal: 6:00 is 0.25, 12:00 is 0.5, 18:00 is 0.75, and 24:00 is 1.00
To convert a time value to a number value, just multiply by 24 (0.5 * 24 = 12.0)
to convert a number value to a time value, just divide by 24 (12.0 / 24 = 0.5). If you format 0.5 as a "time" value, you get 12:00.

Remember to change the post to solved (top right corner under "thread tools").