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.
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.
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
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?
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.
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,)
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....
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.
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
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.
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.
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:
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??
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?
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.
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
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.
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.
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.
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.
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)
