# Calculating time with three possible pay rates based on time of day

1. ## Calculating time with three possible pay rates based on time of day

So here's my problem, I've got a timesheet estimate form that I'm putting together and I can't get the time calculation to work correctly.

The setup is simple:

I2 = Start Time
J2 = End Time
K2 = Total Shift Hours estimated

Now this is where it gets tricky for me, I need to calculate the hours worked that fall into three possible categories:

Normal time = between 07:00 and 15:00
Afternoon premium = between 15:00 and 23:00
Overnight premium = between 23:00 and 07:00

In an effort to make it easier for the people doing the data entry I'm trying to avoid them entering more than a single start and single end time for each line of data.

I've reviewed several examples and have written so many variations of different formulas that I'm going a little cross-eyed at this point and am in serious need of help.

Attached is the sample I'm working with at the moment....

Timesheet_Test.xlsx

2. ## Re: Calculating time with three possible pay rates based on time of day

I'm sure this probably needs some tweaking.....but I took a shot.
I added a time column (15 min intervals) and the SHIFT next to it.
Added columns N,O, & P for 1st, 2nd, and 3rd shifts.
If they work prior or over the shift times it separates the times by shift.
There may still be some bugs....but my tests were working ok.
Good luck - Hope this helps!!

3. ## Re: Calculating time with three possible pay rates based on time of day

Awesome work. Now, what if we wanted to calculate based on time spent greater than a 24 hour period still broken down per shift and the rates for those shifts? Imagine I send a construction crew to a site that takes 5 days to complete. I bill the client X rate during shift 1, Y rate during shift 2, and Z rate during shift 3, but you would have maybe "19" hours calculated during shift 1, 38 in shift 2 etc.

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