# Help with determining the number of regular vs. overtime hours within a set time frame

1. ## Help with determining the number of regular vs. overtime hours within a set time frame

Hi everybody! I'm new to this site but I have a good amount of experience with Excel. I've hit a roadblock trying to come up with a formula that I can't figure out and was hoping somebody might have an answer or at least a nudge in the right direction. I'm working on a spreadsheet for entering the number of hours worked by employees but I'm trying to have it calculate the number of regular and overtime hours based on the start and end times that were worked and I can't figure it out.

Regular time is considered any time worked between 8:00 AM and 4:00 PM. Any time worked outside of those times is considered overtime. I have four columns that I'm working with that need to be filled in for each employee - start time, arrival at the site, departure from the site, and end time. Basically an employee would enter the time they leave their office location, the time they get to and leave the project site, and the time they get back to their office location. Any time within any of those 4 columns that falls outside of the 8:00 AM to 4:00 PM window would be overtime. Any suggestions???

Here's an example: The employee left for the site at 6:00 AM, arrived at 6:30 AM, worked at the site until 2:00 PM and arrived back at the office at 2:30 PM. That would add up to 6.5 regular hours and 2 overtime hours, but I need a formula to determine that breakdown with the number of regular hours in one cell and the number of overtime hours in another cell. So I guess it's really two formulas, but if someone can give me help with one then I can probably figure the other one out.

The times in each column are selected from a drop down where the user can select a time in quarter hour increments starting at 12:00 AM and ending at 12:00 AM. I'm not using military time but I could change it to that if that makes things easier. The times are formatted as Custom with the format "h:mm AM/PM". I was trying to use the TIMEVALUE function in some way along with the IF function but it keeps giving me an error whenever I try to convert my list of times to serial numbers using the TIMEVALUE function and I can't figure out why. Even when I change the formatting of the cells to one of the Time formats I still get the error.

Thanks for the help!!!

2. ## Re: Help with determining the number of regular vs. overtime hours within a set time frame

Post a workbook with sample data and show what you want. Makes it so much easier to get started.

3. ## Re: Help with determining the number of regular vs. overtime hours within a set time frame

I managed to figure this out after much trial and error woohoo!!

There are currently 1 users browsing this thread. (0 members and 1 guests)