# Calculating Hour Outside Core Hours When All Hours Fall Outside Core

Apr 22, 2014
I am trying to set up a time sheet for staff where it calculates hours worked in and outside of core hours. I can do a calculation to work out what they have worked in and out of core hours providing that part of their time starts of finishes in core times. The problem I am having is when some one works only outside core hours. Our core hours are 6am to 6pm. The problem is when they work from say 7pm to 1am. This is irregular work paid as overtime not shift work.

View 5 Replies
ADVERTISEMENT
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
Sep 19, 2012

I need to calculate average response time, the problem is that it should be based only on "core hours" and that's between 7am - 6pm.

How to create function that will exclude "out ot hours" time (6pm-7am) from the calculation.

View 3 Replies
View Related
Mar 23, 2013

I need creating a formula that will tell me the total number of employee hours worked during a certain hour (6:00 AM) for a particular department. Some employees clock in at 6:00 AM, some clock in at 6:30. Here is the current formula I'm using which only tells me the amount of employees in the department not how many hours worked . I have three employees that clock in at 6:00AM and one at 6:30AM, the total I'm looking for is 3.5 but my formula gives me the result of 4.

C5:C1446 is a list of departments

T1503 is the 6:00 AM

D5:D1446 are the Clock In times

K5:K1446 are the Clock Out Times

=SUMPRODUCT(--($C$5:$C$1446="Shipping"),($T$1503>=$D$5:$D$1446)*($T$1503

View 6 Replies
View Related
Oct 7, 2008

I've taken a look at the task manager, and noticed excel is only occupying 50% of my processor usage, meaning that only half of the 2 cores are being used.

What can i change to force excel to utilise both cores?

View 9 Replies
View Related
Dec 2, 2013

I have a time table which some hours are 3 hours time period. I want to distribute to new table hour by hour this kind of hours, or hour to same hour period.

Attached file, defined example and detail.

View 1 Replies
View Related
Jun 27, 2014

As you can see on the example i have TEST hours.xlsx, I have a file that calculate the money every doctor should take based on the working hours.

Nights, holidays and holiday night have different price/hour.

The excel is working fineâ€¦but now I have to make a formula that separates automatically based on the beginning time and the end time of the doctorâ€™s shift the day hours tha night hours, the holiday hours and the holiday nights hours.

In the excel I have fill the hours Manuscript, I need a formula to do that for meâ€¦

On the yellow cell I have try to find out the formula for the holiday hours but because the day is calculated due to a formula it is not working!!!

Simple Example: A doctor Is working from 21:00- 8:00 (next morning Sunday) he should have 1 simple hour (21:00-22:00) 9 night hours (22:00-6:00) and 2 holiday hours (6:00-8:00,)

View 1 Replies
View Related
Mar 1, 2012

I have a program that outputs a total amount of time that a service was provided. This time is reported in hours minutes [Example: 01.08 (hh.mm)]. I need a formula that will "round" to the closest .25 based on a 7 minute window on either side. Here how it would need to work....

0 - 7 Minutes = .00 or 1.00 - 1.07 = 1.00

8 - 22 Minutes = .25 or 1.08 - 1.22 = 1.25

23 - 37 Minutes = .50 or 1.23 - 1.37 = 1.50

28 - 52 Minutes = .75 or 1.28 - 1.52 = 1.75

53 - 7 Minutes = .00 or 1.53 - 2.07 = 2.00

View 4 Replies
View Related
Jul 20, 2014

2 sample with different 'sum' issue.

View 6 Replies
View Related
Aug 28, 2007

We have a system called Datamart that outputs in excel formatted file.

The output of a duration is : 22.00:8.00:25.00 ( day hour minutes )

I want to be able to add, subtract, average, calculate the 10 fastest/slowests durations from a list of durations in this same format. I have googled and tried custom formatting but excel does not like this format. when I try to sum a range and divide by the number excel gives me 0.

View 5 Replies
View Related
Dec 28, 2006

I am building a spreadsheet to manage 15 folks wages to be able to know how much money has been spent.

We routinely work 40 hour weeks and have a sheet that automatically multiplies hour work by person (Say A1) times their hourly rate (say A2)

My question is how could I right it a formula that allows me to type in their hour work (A1) if it's greater than 40 hours?

Say A1 is 40, A2 =$20.00, A3 =(A1*A2) $800.00

If the A1 is 43 what would I put into A3 that would automatically multiply the additional hours over 40 times 1.5 A2

View 9 Replies
View Related
Nov 4, 2008

In column A I have a date AND time entered. By the way, this is not via cell format, I have manually entered, say today's date and the current time. In column B I have a future date and time.

Basically, column A is the date and time a problem was given to me. Column B would be the date and time I resolved the problem. Now for the formula....Column C needs to spit out whether the problem was solved between 24 and 48 hours OR less than 24 hours OR greater than 48 hours.

View 9 Replies
View Related
Mar 13, 2008

I am working in call center, and I get tickets and i have to meet my Services Level Agreement (SLA) with in pre defined working hours.

My working hours are 8:00 AM to 17:00 PM, Monday to Friday. (this includes 1 hour of Lunch time which is not considered as business hour)

As per the SLA, i have to complete the ticket in 40 business hours, i.e 5 business days.

Suppose a ticket is created on 3/5/2008 10:00 AM. I want to know how much time is left for me to work on that particular ticket.

View 12 Replies
View Related
Jan 12, 2010

I want to calculate employee rostered hours or days off from an exported crystal reports. The problem isn't how to calculate the hours but to calculate accurately when formatting changes occur in the exported report. This is an example for the exported sheet data.

http://farm3.static.flickr.com/2748/...50a38e59_o.jpg

So to calculate data intially using the formula

View 10 Replies
View Related
Oct 3, 2008

I have a ROW of numbers (hours) and I need to figure the hours left. Here is an example of 6 colums, Zeros and blanks are valid entries.

C1 C2 C3 C4 C5 C6 Hours Left

16 8 0 0 0 0

16 8 4 4

8 2 1 0 0

16 0 0 8 4 2 2

8 16 10 8 8

I'm trying to get a formula that will figure out hours left. I've tried a number of times.

View 5 Replies
View Related
Dec 12, 2008

I have a spreadsheet where I record individual staff start time, end times and the length of their break. From this I caculate the hours they worked for the day.

E holds Start, F holds End and G holds Break length. Thus using the following forumula gives me the number of hours and minutes:

View 4 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
Apr 23, 2008

I use this formula at work to calculate business hours from Mon-Fri:

=IF(OR(L10="",L11=""),"",(NETWORKDAYS(L10,L11,$N$2:$N$23)-1)*($Q$3-$Q$2)+IF(OR(ISNUMBER(MATCH(INT(L11),$N$2:$N$23,0)),WEEKDAY(L11,2)>5),$Q$3,MEDIAN(MOD(L11,1),$Q$3,$Q$2))-IF(OR(ISNUMBER(MATCH(INT(L10),$N$2:$N$23,0)),WEEKDAY(L10,2)>5),$Q$2,MEDIAN(MOD(L10,1),$Q$3,$Q$2)))

where Q3= business start time 8.30am

where Q2= business end time 5.30pm

thus the difference between 18-Apr-08 16:30 and 21-Apr-08 13:30 is 6 hours.

I now need to adapt this formula for another Department that also works on Saturday from 8.30am to 5.30pm.

View 9 Replies
View Related
Jun 3, 2008

This may seem like an odd request... I recently received a protected worksheet that I entered my date of birth into, and it told me how long (if I pressed F9) I had been alive in y/m/w/d/m/s etc. I was wanting to do a similar spreadsheet to show how long I had stopped smoking for, and a running total of how much I was saving, but I couldn't look at the spreadsheet for help. The stop time was 01/06/08 - 05:00am GMT, up until present, and buying cigarettes I was spending £0.00004166666 per second. Is it possible to create such formulas?

View 9 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
Mar 2, 2010

The below formulae allows me to see the difference between two dates and only returns the difference in working hours ie :

Difference between

02/02/2010 08:00 & 03/02/2010 08:00 is 16 Hours 0 Minutes

=(INT(A3)-INT(C6))+MAX(MOD(A3,1)-MAX(MOD(C6,1)))

The following displays it in the Hrs and Mins format

=TEXT(B15,"[h]")&" Hour"&IF(OR(TEXT(B15,"[h]")+0=0,TEXT(B15,"[h]")+0>1),"s "," ")&MINUTE(B15)&" Minute"&IF(MINUTE(B15)1,"s ",""))

View 9 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
Jan 29, 2010

Format Time Cell For Greater Than 24 Hours: Hours & Minutes Only .....

View 9 Replies
View Related
Feb 20, 2014

Example: In cells A1:A10 random number between 0 & say 20, need to sum ABOVE 8 = (calculating overtime hours)

E.g.

A1 = 0

A2 = 8

A3 = 8

A4 = 10 (giving 2)

A5 = 12 (giving 4)

A6 = 5

A7 = 13.5 (giving 5.5)

A8 = 8

A9 = 0

A10 = 16 (giving 8)

A11 = (Total overtime above 8 hours) 2+4+5.5+8 = 19.5

Need to be able to increase rows and drag across.

View 3 Replies
View Related
Mar 18, 2014

I'm trying to calculate shift working hours without using dates.

The scenario is

Cell A1 = Start Time

Cell A2 = Start Time

Cell A3 = Break Time

Basically I want the output to calculate hours worked between:-

0000 and 0600 as a total in cell A4

0600 and 1800 as a total in cell A5

1800 and 0000 as a total in cell A6

Then if the value of A5 is greater than A3, subtract A3, but if the value of A5 is less than the value of A3, A5 should be zero and the remainder of the value of A3 subtracted from A4 (or A6) depending which has a value.

View 5 Replies
View Related
May 2, 2008

I need to calculate time taken to fix a piece of equipment.

A1 B1

BREAK TIME FIX TIME

4/22/08 23:00 4/23/08 04:00

Should be 5 hours, but i can't find the formula to make it work.

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
Nov 30, 2012

I thought I had everything worked out with this timesheet but I've discovered one more problem.

Weekly Timesheet.xlsx

The total overtime hours needs to show that anything over 40 hours in the Total Regular Hours cell is overtime. And it also needs to show only up to 40 hours in the Total Regular Hours cell.

View 8 Replies
View Related
May 31, 2014

Im doing a spreed sheet to calculate employees hours. I have the employees names in column R and their hours in column S. example R5 = Mike S5 = 8. I need to search column R for all the mike's and total his hours, then move to the next employee and so on... I have already sorted the employees names so that all the names and hours correspond and starting in column R5 and S5 until the proceeding row is blank and then paste the results in column B5 (employee name) and D5(total hours)

View 4 Replies
View Related
Aug 15, 2010

how can i calcaulate working hours between two days.

Working Hours 07:30 - 14:30

e.g. mm/dd/yyyy

start time 01/04/2010 17:34:58

stop time 01/05/2010 08:23:35

View 9 Replies
View Related