# Break Down Daily Hours Worked Into Rate Categories?

Aug 20, 2014
I need to expand on this formula.

I require a formula that will break down daily hours worked into rate categories eg Normal Time, Time & Half, Double Time.

eg. Column E = Total time worked

Column F = Normal time

Column G = Time & Half

Column H = Double Time

What I would like to do is enter hours into Column A and a formula in Column B will split of hours to a maximum amount of 7.6hrs then the remainder of the hours be placed in Column C to a maximum of 2 hours and Column D, no maximum.

These are the formula I am currently using

column B

=MIN(E2*1,7.6)

column C

=MAX(MIN(E2-7.6,2),0)

column D

=MAX(E2-9.6,0)

I would now like to be able to split the hours over 3 rows x 3 columns

Example 1

Row A B C D

1 3 3 0 0

2 5 4.6 .4 0

3 2 0 1.6 .4

Example 2

Row A B C D

1 6 6 0 0

2 21.6 0.4 0

3 0 0 0

The total hours in Column B cannot exceed 7.6 hrs

The total hours in Column C cannot exceed 2 hrs

There is not maximum for hours in Column D

View 2 Replies
ADVERTISEMENT
Dec 15, 2013

I'm attempting to make a simple time sheet for a handful of employees. I'd like to enter the clock in time and clock out time for each day. The end cell should be the running total for the week. The tricky part for me is having the formula subtract an hour for each day that is over 5 hours.

View 3 Replies
View Related
Jan 13, 2009

I am making a schedule and I would like it to take out a 30 min break if the hours worked is over 6 hours.

I have so far

A B

1 11:00 7:30

=24*(B1-A1)

Gives me 8 hours, I would like it to subtract the 30 minutes only ifthe sum is over 6 and not alter the sum if it is under 6.

View 9 Replies
View Related
May 21, 2009

This is probably a very simple problem that has me going around in circles. I am attempting to set up a time roster, where I simply want to check:

If "end-time" minus "start-time" is greater than 4:00 (hrs), then deduct 00:30 (minutes) and place that result in another cell.

If it is not greater than 4:00 (hrs) then leave unchanged. I have read thru countless examples on the Forum - but I think that such great learning is driving me mad. Although I do believe that I have the correct format [h].mm - but attempts with IF's have got me confused. This is one of those "Looking down the tunnel towards the flickering light" moments.

View 5 Replies
View Related
Jul 30, 2013

I work different length shift some of which are over-night. I'm using the formula

=IF(((C1-B1)*24)<0,(C1-B1)*24+24,(C1-B1)*24)=IF(((C1-B1)*24)<0,(C1-B1)*24+24,(C1-B1)*24)

Where cell "B" is start time and cell "C" is finish time. This calculates hours worked whatever shift I'm on.

Is there anyway I can also deduct a hours rest break if I work more than 7 hours ?

View 6 Replies
View Related
Oct 15, 2007

I have a timesheet where user updates start and end time for various tasks.

I have placed a time capture button in the excel sheet (which is simply a macro saying =now() function)

The user clicks it before starting and after finishing the task. The start and end times are captured in adjacent cells.

If the user starts the work, and goes on a lunch break say for 20 min, comes back finishes the task and captures end time, the time difference will not consider break time which is non productive.

How can I incorporate something like 'pause' option so that before he goes for lunch he can temporarily pause the time.

View 9 Replies
View Related
Feb 21, 2013

Is there a way to conver a persons time spent (given in weeks) to adjust/convert to show per month. Attached is the sheet. Do note that week 2/25 - 3/1 is a combination of Jan and Feb so hours should be logically divided into jan and feb...

Name 2/18 - 2/22 2/25 - 3/1 3/4 - 3/8 3/11 - 3/15 Feb mar

Tom 40 10 0 20 ?? ??

name

2/18-2/22

2/25 - 3/1

3/4 - 3/8

3/11-3/15

Feb

Mar

tom

40

10

0

20

??

??

View 3 Replies
View Related
Jan 13, 2014

Lunch is not paid. Holiday and vacation hours get calculated at the regular pay rate. Overtime is anything in excess of 8 hours per day and/or in excess of 40 hours per week and/or over 5 working days per week. Saturdays for most the employees will be overtime because it will be their 6th workday of the week; but it will be regular time for one employee as it will only be his 5th workday of the week.

For accounting and payroll purposes, we need the totals to display in both hour and decimal format.

So far, I have Lunch, Regular and Overtime hours figured out, but I still need to work with Saturday, Vacation and Holiday hours. Also, currently, the time in and out has to be typed in with the colon and AM or PM. Is there another way to input the info without having to type in those items? I'm trying to make it as user friendly as possible.

View 2 Replies
View Related
Aug 11, 2010

I have been working on a timesheet but the problem I have come across is calculating actual hours worked only in the core hours and any work outside the core hours is calculated in the outside hours column. A standard work day is 7.6 hours working between 8.30am and 5.00pm. However if someone was to commence work either before 6am or after 8pm this is outside of core hours. I have attached an example of my timesheet for you to see what I am talking about.

View 3 Replies
View Related
Apr 18, 2006

I am trying to calculate earnings based upon hours worked based on 24 hours format (time out - time in = time worked) multiplied by hourly rate. I cannot get the proper calculation. How can I attach a copy of the Excel worksheet?

View 9 Replies
View Related
Nov 8, 2008

Some years ago I came across a formulae to calculate Daily Interest on a Building Society Savings account in the UK. I have used this since but find my calculations never work out the same as my BS, although to my advantage! It is =B3*B4/360*DAYS360(B5,B6,TRUE) Where:

B3=Capital

B4=Interest Rate

B5=Starting Date

B6=Finishing Date

For some reason the formulae uses 360/year and not 365/year. Using both still gives wrong answer.

View 2 Replies
View Related
Sep 25, 2013

I am trying to create a a file that will serve as time log and dashboard report for consultants who work a maximum of two hours a day, with a strict(fixed) calendar schedule with multiple sessions. Some consultants may work one hour of a particular schedule on category A(Lead) and the second hour as category 2. How to create a sheet that sums the rates for all the categories worked on a given day without using a macro. Here is my sample data

Pay Categories:

Lead

Assistant

Admin

Pay Rates

Lead-$75/hr

Assistant - $40/hr

Admin - $20/hr

Sessions and Session Dates sample:

Session 1 - 10/8/2013 to 11/8/2013

Session 2 - 11/15/2013 to 12/15/2013

Session 3 - 01/06/2014 to 03/15/2014

*Note the dates will or should be listed in separate columns in order for them to be used for daily logging.

Employees (Sample)

Jane Doe

Jack Doe

John Doe

Desire goals of the report:

1. Daily log as employees work e.g if Jane Doe works on 10/8/2013 as a Lead and Assistant, ideally the data entry person should be able to enter these two values on one cell or pick the relevant value from a set list

2. The report would then match the value of the data in the cell for Jane Doe on 10/8/2013 with a table array with categories in one column and the pay rate in another, and return the appropriate pay value which can either be a sum of the two rates or the total hard coded into the cell.

3.Be able to create a dashboard report that would sum the totals by employee, month,sessions etc in a separate sheet vs other criteria such a budget etc.

View 1 Replies
View Related
Sep 24, 2012

Timekeeper to tally total hours worked by employee. When doing a pivot for sum total hours worked for FY13 it does not calculate correctly. I understand they formatted that column/custom h:mm but when I change the 8:00 hours to a number I come up with 0.33 .

I am attaching a sample file : sample time.xlsx

I just want them to get a running total of hours worked/pay.

View 5 Replies
View Related
Jul 3, 2014

I'm trying to make a way to track if I've worked more or less than the 39 hours/week I'm paid for. At the end of each week, I have a total of how many hours and minutes that I've worked .

On column C I have what I should work.

On column D I have what I did actually work

On column E I'd like to convert automatically Columd D to minutes for calculation purposes

Column F to know if I worked more or less than what I should've subtracting C and E

Column G to have an ongoing tally to know if I need to work more or less

Column H and I could probably be the same thing. Ideally what I'd like is to have a formula pull the information from column G and put it into workdays, hours, and minutes with 1 workday being 7 hours and 48 minutes.

Since I tend to work too much, I'd like to know if I've worked 3 days too much during 1 month, I can take 3 days off the next month to get everything zeroed back to where I don't owe the company anything and vice versa.

View 6 Replies
View Related
Dec 2, 2008

I am trying to create a spreadsheet that auto calculates my emp. time.

However I do not want to use military time. I can get it to work by =a2-a1 but only if it is 8.5 and 17.5. Any ideas how I can do clock in 8:30 clock out 4:30 = 8 hours?

View 7 Replies
View Related
Feb 10, 2010

I'm trying to calculate the total hours worked for two given periods over a shift , which can span two consecutive days ie. start 15:45 and finish at 00:15 the next day. Hours worked between 6am and 6pm are paid at standard rate, whilst hours worked between 6pm and 6am attract penalty rates. Hours are cacluated in 24hr time

I have attached a copy of the timesheet that we use so you can see exactly what I'm trying to achieve, and included most of the shifts that we have.

View 14 Replies
View Related
Jan 1, 1970

I can do the timesheet formula for adding the hours worked as follows:

Start Finish Total

08:45 17:15 8.5

However, it doesn't work when I fill in a whole week work of hours in this format:

Start Finish Total

08:45 17:15 08:30

It works on a daily basis, but when total hours exceeds 24, the formula get's all mixed up - how to I format the total column to account for every 5 minutes worked, which you can't do when converting to decimal??

View 14 Replies
View Related
Nov 27, 2012

Weekly Timesheet.xlsx

This spreadsheet calculates hours worked great for first and second shift but when you enter times for third shift it goes all whacky with the outcome.

View 10 Replies
View Related
May 9, 2008

I am creating a spreadsheet that will track hours of overtime worked and within the spreadsheet there are several separate departments listed.

I have made it dynamic so that the summary spreadsheet will update as employees are added. I’m using a macro and some complicated helper cells to be able to sort the employees based on their total OT hours worked.

View 13 Replies
View Related
Mar 15, 2012

Is there a function or a macro to calculate number of hours worked from a single cell value.

For example, cell A1 has "1600 - 1715" and need it to convert to "1.25" on cell B1

View 6 Replies
View Related
Feb 24, 2009

I have this spreadsheet and in it the time is changed from military time to regular and then I use a formula to calculate hours worked. On some of these the total is off by one minute. Does anyone know how to fix this?

I don't know how to paste the spreadsheet so you can see formulas,

View 9 Replies
View Related
Feb 1, 2007

I need to develop a work sheet for agency booked people to count the number of hours worked by them on daily basis. Agency can clock in at three different times and clock out at 6 different times. i tried but could not even develop logic to calculate the total earned hours. i attached the sheet for reference.

View 5 Replies
View Related
Dec 21, 2013

Refer to the attached sheet which is Daily Schedule for employee. I need a formula to add hours worked on single day in cell C2 for Monday, E2 for Tuesday, G2 for Wednesday, and so on.

Every day we have Clockin_Clockout info for each employee as shown for employee a & b.

FYI : I am using below formula to add employee hours for the week as (formula in cell R4).

[Code] .....

View 14 Replies
View Related
Jan 28, 2014

Attached sheet, I am struggling with the formula that will add up the hours overtime worked per day when I enter start and finish times.

Standard working hours are :

mon to thurs 8 hrs per day 8m to 16.30 (with 30 mins unpaid break)

Friday 6 hrs per day

Saturday all hours are overtime

Hours Commited sheet.xlsxâ€Ž

View 4 Replies
View Related
Nov 6, 2009

i m trying to work out the productivity of employees based on how many hours they work (Time in Back Office). How many pieces of work they complete(Back Office items Completed) if 1 piece of work should take 7 mins. the item in red is what i cant seem to figure out.

View 5 Replies
View Related
Nov 11, 2009

to calculate how long a ticket is open in our system before being resolved. I don't want to count weekends, and if the ticket is 'suspended', I don't want to count that either. There is also the factor that the ticket 'un-suspend' date may be later than the ticket 'closed' date. Which is the bit that's throwing me.

So, I have the following fields

Ticket Open, Ticket Closed, Ticket Suspended Date, Ticket Unsuspended Date

A sample ticket might be (using above fields)

02/11/09 09/11/09 04/11/09 30/11/09

That 'should' equal two days (16 hours) as the Unsuspend date falls after the close date so it was suspended from the 4th until closure.

Now I want to know, in hours (8 hour day) how long that ticket took to resolve (i.e close), remembering you can't count the time it was suspended, or any time that fell over a weekend. Also not all tickets are suspended.

View 13 Replies
View Related
Dec 15, 2009

The whole document works but the last one i need. I'll post it all just in case i have an error elsewhere.

Start Time -- Stop Time -- Break -- Total -- Hours Worked -- Average -- Pay

6:00 -------- 3:00 ------- Yes ---- 1600 -- 8.00 ----------- 200 ------ $xx.xx

E2 Formula for time: =IF(C2="Yes",(((A2*24+12)-(B2*24+24))+1),((A2*24+12)-(B2*24+24)))

F2 Formula for average: =D2/E2. and now for the problem one. G2 Formula for pay: =IF(F2<165, E2 * 7.25, IF(F2<180, D2 * 0.07, IF(F2<190, D2*0.08, D2*0.09)))

Just to break that formula down more:

0 - 164 = 7.25 * Hours Worked

165 - 179 = 0.07 * Total

180 - 189 = 0.08 * Total

190+ = 0.09 * Total

it only calculates 7.25 by the hours worked no matter what comes up in F2

View 5 Replies
View Related
Jan 22, 2009

I'm trying to figure out what is wrong with this formula. =(SUMIF(Q14:Q4995, "<="&EOMONTH(TODAY(),0),W14:W4995))-(SUMIF(Q14:Q4995, "<="&EOMONTH(TODAY(),-1)+1,W14:W4995)). I've got a cell that adds Total hours worked which pulls from the same column of entered data as the formula above and that cell works. My hours for the month however just shows up as zero. If I try and edit the formula or even just highlight it to copy it and then tab out of the cell this shows up...

1/0/00

If I undo the highlight and tab it will go back to showing zero. I've checked my dates that I entered and they are correct. I'm at a loss as to how to fix this formula.

View 4 Replies
View Related
Mar 30, 2012

I need to create a formula that assumes 40 hours = 1 FTE (full time employee). As an example if I have a total of 100 hrs I need to know how many employees to hire. So in this case it would be 2.5.

View 1 Replies
View Related
Feb 26, 2013

In a single employee Timesheet spreadsheet:

....................Mon.....Tue.....etc.

In:................6:65.....6:71

Out:............12:07....12:05

In:..............12:62....12:41

Out:............15:66....15:73

Hrs Worked:.....8.46......

How to format the cells in the daily columns and what formula to calculate 'Hrs. Worked'?

View 2 Replies
View Related