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 RepliesLunch 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.
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.
If a person does a full day I want the formula to calculate how many hours minus 30 mins for lunch i.e. 7:30 - 4:00 = 8 hours. I have done this and it works fine, the problem comes when a person does a half day i.e. 7:30 - 11:30, this will read 4.5 hours. If this is the case I don't want the 30mins deducted so it should be 4.0 hours worked.
I have high lighted a column in the work sheet (yellow) whereby the user puts an 'x' in the appropriate cell if they are working a half day.
The probem is I cannot incorperate the two formulas in one cell (the Actual hours cell) I have the one formula in the actual hours cell and the other bolted on the end thus giving two different readings.
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.
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.
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
??
??
basically I am trying to make a time calculator for work, I want it to automatically add the hours up but then also minus break times and then give me a whole paid total hours worked that week.
CALCULATOR.xlsx
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 ?
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
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.
I have a data of complaints where I need to present it to the Management in such a way that the SLA period of 8 hours does not pass. Our office working hours are 7 AM till 7 PM. The complaint received should be escalated to concern section within 8 Hours of SLA time. I have the list of dates with received time. The complaint which could not be escalated today would be escalated next day. In this case is should deduct 12 Hours (7 PM to 7 AM, Non-working hours) from the time. How can I insert escalation date so as that it would deduct non working hours from it.
View 5 Replies View RelatedI 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 A B
1 4/1/09 12:15
2 4/2/09 10:00
3 4/4/09 8:10
4 4/6/09 9:00
5 4/8/09 5:00
6 4/11/09 7:00
I need to add up hours worked of last 3 days including current day. The date column does not contain everyday's date. How do I formulate a cell for date entry and another cell will show the sum of last 3 days including date not shown and date entered? E.g. If I enter date 4/6/09, result should be 17:10 hrs; 4/11/09, result should be 7:00.
I need also to calculate difference between dates(dd-mm-aaaa hh:mm) in workhours ( hh:mm):
The work period is 9-18 with lunch interval 13-14 The startdate and end date could be out of the work hours and i can't include the extra hours. I can have several days (workdays) at the difference, but i should maintain the format hh:mm.
Ex1:
Startdate 05-03-2013 18:34 ( date to calculation should be 05-03-2013 18:00)
end date 06-03-2013 10:30
Time Difference 1:30 ( from 9 to 10:30 of 06-03-2013 )
will be equal to:
Startdate 06-03-2013 8:34 ( date to calculation sould be 06-03-2013 9:00 )
end date 06-03-2013 10:30
Time Difference 1:30 ( from 9 to 10:30 of 06-03-2013 )
Ex2:
Startdate 06-03-2013 12:01
End date 06-03-2013 14:28
Time Difference 01:29
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 am using time based on the 1904 calendar, this is so time can be shown as a minus figure when to cells are taken away and the result is say -17:25. What I want to do via conditional formatting is show any minus hours as red. Tried various options but I have had no luck so far. or can this be done via VBA?
View 6 Replies View RelatedTimekeeper 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.
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.
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?
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.
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??
I have a time calculated and I want to know, Out of that time how much is above or below 8 hours.
Ex. 1: 7:30 - 8 = [result] "-:30" mins
Ex. 2: 11:00 - 8 = [result] "3:00" hours
OR
How can I convert 7:30 into 7.5 [decimal] and then subtract that from "8" to get the difference?
I know this is an easy one, I have done it many times before. It's crunch time and I am drawing a blank.
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.
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.
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
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,
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 RelatedRefer 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] .....
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‎