# Calculate Hours Worked After Specific Time

Feb 6, 2008
i have a timesheet that we are trying to use. the problem is the column that says shift diff. if an employee works after 6:30pm for 1and 1/2hr, he is entitled to shift hours. shift hours is between 6m and 8am.

As long as he works after 6.30pm but works for at least one and a half hour, he will get the shift.

if work, 9am to 7:30pm, and have break between 2-3pm, should have 1.5hrs shift and 9.5hrs total

if work, 7:45pm-9:45pm, and have break between 8:30-9pm, total hrs work is 1.5 and shift hrs s/b 1.5hrs

if work 3pm to 12am and have break between 7-8pm, total hrs work is 8 and shift hrs s/b 5hrs

View 9 Replies
ADVERTISEMENT
May 14, 2013

I need a formula to calculate hours worked between specific times. In the worksheet I have, I need to calculate the hours worked between 5am and 10pm, the start time is in C7, and the finish time in D7 the formula need to be in H7, and another formula in I7 to calculate hours worked before 5am and after 10pm .

C

D

F

H

Iï»¿ï»¿

Start

Finish

Total

Ordinary

20%

4:00

23:00

19:00

17

2

View 9 Replies
View Related
Sep 17, 2012

Any way to calculate the total hours staff work based on the mininum time of the first transaction to the maximum time of the transactions. I used a DMIN and DMAX function to get those times per employee. The issue is then the time goes over from one day to the next, such as from 11 PM to 4 AM the next day. As you can see in the data below,the fourth record shows the minimum time as 12 AM and the max as 11 PM with total time worked as 23 hours. In this example, the total hours worked should be five hours.

min time

max time

total hours

7:00 AM

16:00

9:00

[Code] .......

View 4 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
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
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
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 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
Jul 21, 2006

i am trying to make an excel spreadsheet so i can keep track of my hours and pay at work... i know how to do mult and add but i wanted to know how to do the 2 together.... for example

Hours ST OT Total Hrs Gross Pay

8 2 10 ?

i would like help figuring out the gross pay if say for example my st time rated is 21.21 an hr, and the ot rate is 31.82 how can i get the 8 times 21.21 = 169.68 and the OT 2hrs times 63.64 to show up in the gross pay as a total of 233.32 - what would the formula look like?

View 3 Replies
View Related
Sep 5, 2006

I got a problem with a time calculation, if it's even possible.

I want to calculate the amount of hours that have been woork in a slot.

Example:

First slot is from 08:00am till 17:00pm, time cost 100%

Second slot is from 1700am till 24:00, time cost 110%

Third slot is from 24:00 till 08:00 am, time cost 120%

I'm working from 08:00am till next day 01:00am

So my hours are:

9 in slot 1,

7 in slot 2,

1 in slot 3.

Is this possible to calculate in Excel?

I got a file attached with a sort of layout.

View 8 Replies
View Related
May 5, 2006

I have a user form with textBox1 = start time (entered as "[h]:mm") and text Box2 = finish time (entered as "[h]:mm"). I would like textBox3 to display the difference between the start time and finish time as a general number!

For example

Start time: 21:00

Finish time: 06:30

Hours worked: 9.50

Start time: 12:30

Finish time: 23:00

Hours worked: 10.50

View 9 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
Feb 14, 2008

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: ...

View 4 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
Sep 25, 2013

I'm trying to create a time sheet to calculate how many hours worked in a week, Once it reaches 40 hours, The excess over 40 hours goes into a "overtime" cell. The "40" hours remain in the regular hours cell.

Attachment 267704

View 14 Replies
View Related
Aug 4, 2013

My overtime pays is anything exceed over 8 hours per day or over 40 hours per week. Right now I can only calculate overtime by either over 8 hr/day or over 40 hr/ week. I need a way to combine both.

View 9 Replies
View Related
Aug 6, 2007

If a Rescue Officer is called out at 23:00 and is back at 04:00, this should equate to 5 hours worked.

It seems that if my times are all on one side or the other of a 24 hour cycle, my calculation work fine but it it breaks across the 24 hour (as above, it doesn't work.

A2=04:00

A1=23:00

Using (A2-A1)*24 give me -19.00 hours

My SS macro has a line:

s = (wks.Cells(c, 3) - wks.Cells(c, 2)) * 24 'calculates the duration of time worked

Is there any way of getting excel to calculate an elapsed time in hours when the start and end times roll over from one day to the next?

View 9 Replies
View Related
Feb 16, 2012

I charge one rate for day work and one for night shifts. My spreadsheet is set to figure the total number of hours worked and I know how to multiply by dollars to get answer #1, but is it possible to use a formula to multiply times a different rate for a night shift?

For example I use =IF(B2

View 9 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 19, 2009

Is it possible that a cell contains both numeric and alphanumeric data and to do calculations on that?

For example: if a cell conatain the value "10a" or "8.5b" etc. Would it be possible to have a column that gives me the hours worked (the numeric value in the cell) and a line that gives me the amount of people that are working on shift "a" (the alphanumeric value in the cell).

Is this at all possible? Or does that require VBA/Macros and stuff (in which case this is posted in the wrong part of the forum )

View 5 Replies
View Related
Apr 4, 2013

find a formula that will calculate the hours between the two below values but only take in to consideration the business hours (from 9 to 17) and exclude any weekends?

08/03/2013 13:32:00

02/04/2013 09:32:50

View 4 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
Aug 15, 2007

I need to create a formula to calculate monthly hours worked (144.20) by hourly rate (£14.25). So far everything ive tried has given me awrong answer. I cant seem to make the total right. From what ive come up with (Not good) the total accepts the hours-just not the minutes.

View 8 Replies
View Related
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
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
May 1, 2008

I need to calculate the hrs between two date and time and deduct any time outwith the hrs of operation (09:00-19:00), if the date and time falls outwith these times.

I have used the formula 24*IF(A2>G2,G2+1-A2,G2-A2), where G2 IS 21/04/2008 11:45 and A2 is 20/04/2008 00:22, but can't get it right.

View 6 Replies
View Related
Jul 20, 2012

I want to calulate time by decimal the problem is anything after 1 am wont work

example start at 12:25 finsh at 1:45 time should show up as 1.33

using excel 2007

View 3 Replies
View Related