As per attached spread sheet in the yellow highlighted cells, I am trying to input a formula to automatically calculate the hours between specific times for shift workers so I can easily calculate their penalty rates however I just cant seem to get it to work, the main issue being midnight.
View 9 Replies" =(C2 >D2)*MEDIAN(0,D2-1/4,1/2)+MAX(0,MIN(3/4,D2+(C2 >D2))-MAX(1/4,C2)) "
approach to sort out Day/Night Hours. Its bomb proof!
A new situation demands overtime payments......start and finish time can be any time day or night (crap job!), overtime is payable after 8 hours. Thus I have day (0600-1800) standard rate, day (0600-1800) overtime rate, night (1800-0600) standard rate, night (1800-0600) overtime rate.
So, starting at 1400 and finishing at 0100 give 4 hours day std + 4 hours std night + 3 hours night o/time; whereas starting at 0200 and finishing at 1300 gives 4 hours std night + 4 hours day std + 3 hours day o/time.
I'm using Excel 2003 and 2007 so use the Excel 97-2003 format.
I am working on a project involving calculating time. It is a timesheet calculation. I was able to design the following layout:
.....A............B..........C..........D.......E.....F
1....Date.........Time IN....Time OUT...Hours... Total
2....01/01/07.....1830.......1930.......01:00...01:00
3....01/02/07.....1930.......2330.......04:00...05:00
4....01/03/07......830.......1900.......10:30...15:30
5
Column A is formatted for DATE. Columns B and C are GENERAL. Columns D and E are DATE format customized as '[hh]:mm'
The formula to calculate the time difference between the numbers in column B and C is located in column D. It is as follows:
=IF(C4<1000,TIMEVALUE(LEFT(C4,1)&":"&RIGHT(C4,2)),TIMEVALUE(LEFT(C4,2)&":"&RIGHT(C4,2)))-IF(B4<1000,TIMEVALUE(LEFT(B4,1)&":"&RIGHT(B4,2)),TIMEVALUE(LEFT(B4,2)&":"&RIGHT(B4,2)))..................
I have enclosed a sheet with the dilemma i currently face.
Ive tried multiple variations on a solution none of which have been 100% accurate.
Basically the work day is split into 3 shifts :
Days ( 06:00 - 14:00 )
Afters (14:00 - 22:00 )
Nights ( 22:00 - 06:00 )
I have a report which tells me the total time the colleague will be getting paid for and there
clock in and out times.
I need to determine which shift bracket there hours fall into based on the time bands.
Ie :
David worked 8 hours , started at 10:00 finished at 18:10 , so thats 4 hours recorded in days and 4 in afters since he worked across both shifts. the 10 minutes is not being paid so it doesn't need to be recorded.
the sheet should explain things better.
I have an Excel sheet where users enter shift start and finish times (normal Excel time format) - for example: A1 might be 18:00 and A2 might be 06:00 for an overnight shift from 6pm to 6am.
All I need to calculate from these times are the number of hours to which an allowance applies, under the following conditions.
The allowance is paid for all hours worked between 18:00 and 06:00.After 10 hours, a shift becomes overtime and no shift allowance is paid.Shifts are regularly worked overnight (i.e. past midnight into the following morning)
Example scenarios include:03:00 - 15:00 would pay 3 hours of shift allowance09:00 - 21:00 would pay 1 hour of the allowance (as the shift becomes overtime from 19:00)03:00 - 21:00 would pay 3 hours19:00 to 08:00 would pay 10 hours (as the shift becomes overtime from 05:00 the following morning)22:00 to 10:00 would pay 8 hours
And so on.
I feel like I’ve got most of the pieces of the jigsaw, but I can’t put them together! I’ve got the following formulae working out bits of what I think I need:
[Code] ........
The above works out the number of hours before 6am and after 6pm respectively (which I can then SUM), and I believe also accounts for overnight shifts. This obviously doesn't include the more-than-10-hours criteria yet.
The larger formula now looks like this:
[Code] ......
…but this still doesn’t work properly! The sections referring to 1.25 were my attempt to get the shift allowance to stop if the past-midnight shift continues past 06:00, but I don't think it works properly.
I also know I’m probably using *24 and /24 more than I need to, but that’s partly so I’ve got a better grasp of what the formula is doing.
Once this is working, I'm happy using an IF… >10 formula to prevent the total number of hours of shift allowance being more than 10. However, I’m really struggling to find one single formula that will factor in shifts that might start before 6am and finish after 6pm (i.e. 05:00 – 19:00, which should pay one hour), shifts that might go past midnight and possibly past 06:00 the next day, and so on.
Lastly – not to try and complicate things further – there is an optional cell elsewhere, say A3, where a user enters ‘Y’ if the individual takes an unpaid 30 minute break at some point during the shift. This is to be deducted from whatever type of hours are being paid at the end of the shift. For example, if a shift is from 01:00 - 11:00 with the break, it would pay 5 hours with a shift allowance and 4.5 without. If the shift was 13:00 - 23:00, it would pay 5 without the allowance and 4.5 with the allowance. Is there a practical way of doing this, or does this become much more complicated?
I m working with a workbook containing a work sheet for every day of the months productivity. Each work sheet contains the employees name and ID number in first two columns with 18 additional rows of data. I've created a master sheet which keeps track of the entire month production but I would like to be able to count the employess from each of the 28-31 sheets within the workbook.
On a given day depending on the day of the week, I may have 8 on staff or 27. Names and ID number are always in the same columns from a different reporting software which generates excel reports.
I have three columns where my workers record the amounts of work they do during the day. The columns are labelled as follows:
Column A – “Correspondences’’
Column B – “Linking’’
Column C – “Allocating”
When a task has been completed an “X’’ is put in the relevant column (which is then summed at the bottom). I use these sums to calculate each workers productivity in the following way – number of “x’’ divided by number of days worked. This is a simple formula, however, each “x” now equates to time – which is causing me problems. An “x’’ is Column A equals 1 hour, Column B 1 hour and Column C 2 hours.
I am looking for someone to help me create a formula which will calculate all possibilities in the above situation above. Please bear in mind that a worker can do one of these tasks during the course of a week, two or all three .
I have the basics set up, but need to work out how to make it calculate my pay per shift dependant on the type of shift i have worked.
I have attached a screen shot of the current page,
In it i have currently used validation drop boxes for the location and worked columns with tables just to one side of the sheet.
The shift pay is the column i am having trouble with.
I would like it to change dependant on what is selected in the 'worked' column.
For most things it should just display basic plus holiday, however if supervisor is selcted in the work column, it should display basic plus holiday plus supervisor.
a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!
examples:
start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333
start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000
start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000
Function prod(st As Date, en As Date) As Double
Dim shour As Integer
Dim smin As Integer
Dim ehour As Integer
Dim emin As Integer
Dim stod As String
Dim etod As String
pday = 8
pnight = 12
shour = Hour(st)
smin = Minute(st) + shour * 60
If (shour >= 8 & shour < 20) Then
stod = "day"
Else
stod = "night"
End If
ehour = Hour(en)
emin = Minute(en) + ehour * 60
If (ehour >= 8 & ehour < 20) Then.................
I have a Start Shift and an End Shift time,
Start Shift = 2009/11/10 09:27:06 (GMT-6:0)
End Shift Time= 2009/11/10 15:13:03 (GMT-6:0)
eg. Total Time = 5.3 hrs
I would like to take if from this format, and calculate the total time difference. Sometimes the GMT codes may be -5:0 if that means anything. For the cell "Total Time" I only need it to have a decimal format.
This one should be a bit more simple, (vlookups I think)
I have a list of clients, and client codes
so:
CODE_____CLIENT
001 Mr. A
002 Mr. B
003 Mr. C
And on the time sheets, we must put the client, and the code.
So
0004 Mr D
But we have to type that in manually (code and client)
Can we use a formula, so that when we type the client, the code will appear? Granted that the name will have to be exactly perfect.
Also, how it it possible, to make a list of possibles to appear, when typinig?
eg, if I type Graem
a list will appear underneath saying the possibilities.
such as
Graem
-Graeme A
-Graeme B
-Graeme C
ETC.
See workbook attached.
I'm looking for help to detemine rates so it automates in the sheet.
Can you give me assistance and code perhaps ? I'm pretty basic at V-Lookup and If functions. Is this the best route to take ?
All is explained within the workbook.
Not sure where the best to ask this is so i'll do it here.
I have a h:mm time which i need to get converted into days/hours/minutes, creating an on the fly phrase of something like "2 days, 4 hours, 32 mins" for example.
eg: 26:45 (hours/minuts) to be converted to "1 day(s), 2 hours, 45 minutes"...
I am having trouble with this formula
=IF(E4-D4 < 1/24*7.1,E4-D4,E4-D4-1/24)*24
it works well unless the staff member works past midnight. I get a negative hours worked value returned.
for eg
E4=8AM and D4 is 5PM i get an answer of 9 hours in F4, this is all good but if the start time E4=4PM and the finish is D4=1AM then I get the result of -15 hours in F4
I have a system that enters the ID in the first column, the date and time in the second and third columns and the sense (IN/OUT) in the fourth column, for each employee that enters/exits the premises. Note that not only the in /out can occur over midnight, but also I have the situation of having two periods of the same employee in the same day.
The objective is to obtain in some way a daily report for each ID (employee).
we work 8hrs 30mns for 4 days 7hrs 30mns for 1 day this is monday to friday
using Excel i need a formula that will add the following:-
a1 8.30
a2 8.30
a3 8.30
a4 8.30
a5 7.30
total 41.30
I am trying to make a timesheet in Excel 2007 with a formula.
I want it to read:
IN = 8:30 AM, OUT = 11:30 AM, IN = 12:30 PM, OUT = 4:30 PM
The total hours will be 8 because there is an hour for lunch.
And if I work a few hours one day and leave before lunch, I want the calculation to be right. I found a formula but it wouldn't add the lunch hour and I added a +1 in the formula but it makes the calculations wrong for when I work for only 2 or 3.
When people enter their hours I get them to do it in 24hr format, fine. BUT my problem is coming when I'm working out wages etc. I can get the user to enter 09:00 (start time) and 17:30 (end time) but then the cell works out the hours (cell 2-cell1) gives 8.30 in time format when I need it to show 8.5 (total hours worked)
This means when it goes to work out wages, it takes 8.5*hourly rate not 8.3!!
I have made a work schedule for my local business and have set up a series of formulas that will fill out time cards that I could print out directly onto the paper time cards. The formulas that I have work except that if there are two subsequent entries that later will not return a value and result in an error.
If you could take a look at it that would be awesome. To use it you just need to type a name into the name column and a work time into the time column for that day. then in the other sheets( one for each worker ) it will set up the time card. The the error happens on Thursday, when Bob has an entry right after Fred. Then on Bobs sheet it gives me a #N/A.
i am creating a weekly time sheet for my company.the problem that i have is when the persons time reaches 40 hours, the time needs to be calculated in the overtime field. this is really tough for me when the person reaches 40 hours in the middle of the work day. I cant figure it out. i have attached the spreadsheet if you would like to look.
View 2 Replies View RelatedI have an Excel timesheet, and I am wondering if there is a way to have the timesheet default to PM after 12 noon, so the employees dont have to put in PM, they would just put their time and the sheet would default it to PM.
View 9 Replies View RelatedI am making a timesheet
I have
Start Time, End Time, Break, Hours Worked. Then on the right hand side of my spreadsheet I started playing around with the current time etc.
I want to work out the time left in a working day(like a countdown), based on a variable number of hours of work in a day (here it is 7 hours) excl. breaksie. 7+breaktimeso I need 7+break - 'hours worked' to get hours and mins left
I worked out how to get hours worked easily enough,
=J58-LOOKUP(TODAY(),A:A,D:D)-LOOKUP(TODAY(),A:A,B:B)
where J58 is a cell that has the current time in it and D and B are the columns with the break time and start time in them.
Hours Worked01:59:48Current Time11:49:4807:00:00Break time: 00:30:0007:30:00
I am trying to subtract the hours worked (1:59:48 in this case) from 7:30:00. The hours worked can be updated every second using F9.
I know it's something to do with negativer times because of dates etc but I don't know what to do to make it work.
I have a timesheet which calculates overtime and the sheet works ok. I've had help on this board constructing it and i'm well pleased so far. what i need for it now, is if there is an input cell with no data in it, i want the results cell to stay blank but at the moment i'm getting those horrid hash symbols.
The formula just now is end time minus start time, and from that a 45 minute break is deducted. The break is always the same so i have that in a cell on it's own and the formula does an end minus start, then - the break. What i'd like is when there's no data in the cells, leave the result cell blank...
I need to keep track of my salesmen's time. However, the company will not pay for the first 45 minutes so I need a formula that says, if the time entered in under "worked" is less than 45 minutes, no time will be deducted. If the time entered is equal to or greater than 45 minutes, 45 minutes will be deducted. These are the columns needed and the Daily Hours would be the total of all the previous columns. WORKED TRAVELED LUNCH DAILY HOURS
View 2 Replies View RelatedI 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.
I am working on a spreadsheet to track hours worked. It is an 8 hr day. I want there to be a running total, but when less than 8 hrs are worked and the total goes below 8 hrs per day (as in when you have no "banked time" it will not display negative or owned time. Also, it doesn't seem to want to skip over the weekends for me.
View 2 Replies View RelatedI have made a time sheet and am trying to have the total hours and grand total- round up to the nearest quarter hour, I.E. (.25, .50, .75. 00), if anyone can help me please it will greatly be appreciated, this is what i have now, in my totals fields:
ROUND(IF((OR(B13="",C13="")),0,IF((C13<B13),((C13-B13)*24)+24,(C13-B13)*G1424))+IF((OR(E13="",F13="")),0,IF((F13<E13),((F13-E13)*24)+24,(F13-E13)*24)),2)
I Have also attached the file so you can see it completely.
Basically I have a timesheet as follows:
Basic Hours Worked
Overtime Hours Worked
Day
Time In
Time Out
Hours Worked
Time Decimal
Lunch Hour
Number of hours
Mon
8:56:00 AM
4:50:00 PM
07:54
7.90
[Code] .......
To determine the hours worked from the times input I am using the following formula:=
HOUR(D65)+MINUTE(D65)/60
These formulas seem to be working fine but what I am stuck on is deducting and adding lunch hours and overtime hours.
The first problem I have is for example if an employee were to type the figure 1 in the Lunch Hour column for each day lunch is taken I would like it to be subtracted from the bottom total.
Also if any overtime were input on any day I would need it added to the totals.
I am trying to figure out a formula that will provide the following results for time worked. (This is contract work that is billed in 1/10 hr increments.)
1 min - 5 min = no charge (rounddown to 0 min)
6 min - 11 min - rounddown to 6 min
12 min - 17 min - rounddown to 12 min.
or
1 - 5 min are billed at 0 hours
6 - 11 min are billed at .1 hour
12 - 17 min are billed at .2 hours
B15 = start time, C15 = end time, G15 = billable time in 1/10 hrs
On Error GoTo importError
For Each b In Range("names")
If b = FILE.Sheets("Sheet2").Range("e3") Then
ThisWorkbook.Activate
ThisWorkbook.Sheets("Sheet2").Select
b.Row.Value = n
For Each c In Range("dates")
If c = FILE.Sheets("Sheet2").Range("e5") Then
ThisWorkbook.Activate
ThisWorkbook.Sheets("Sheet2").Select
c.Column.Value = m
ActiveCell = nm
Set Targ = ActiveCell
Targ = system
Targ = FILE.Sheets("Sheet2").Range("e20")
End If
Next
It doesnt work, it gets to b.row.value and throws up an error, i realise im using the wrong code but I dont know enough vba script to resolve the issue
I have a timesheet and a data base spreadsheet, the db spreadsheet opens the timesheet (many, one after another) and I want it to look for each name in the db and if the name cell on the timesheet it has open matches then i want it to remember the row value (on the db), then look through the dates in the db until it finds the matching date to the one in the timesheet, i want it to store this column value (in the db) so I can concat the row and column to get the activecell where I will be putting the total hours (a single cell reference) from the timesheets into the db.