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 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.
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
I have created a daily schedule which has a number of factory variables taken into consideration which determine the date and time a particular product should, barring any mechanical problems, come off the machine. (see attached spreadsheet).
The date at the top will be editable by me only so that when I update the production quantities, the “date/time off” column automatically re-adjusts to the remaining quantities.
The formulas are a little long winded, but I have left them that way whilst I try and develop it. I should be able to figure out how to condense them later.
My problem is that the “date/time off” on the right works excellent, but over a 24 hr period.
Ordinarily, we work a 12 hour day (6am to 6pm) with overlapping shifts to cover breaks, and 20 mins warm up at the start of the day for the machine, thus maximising a 12 hour day.
Of course if demand exceeds the allotted time we put on overtime.
Is it possible to specify that normal days are only 12 hours so that if a product exceeds 6pm, it flows into the next day with the balance starting at 6:20am?
And, if the production for the week exceeds the time could I stipulate particular days which we deem are suitable for overtime? Ie, we decide Wednesday is a 14 hour day and not 12.
I had toyed with the idea of creating a 365 day table/calendar, on another worksheet which would have its individual allocated hours in an adjacent column and somehow link them to the date/time off, perhaps by way of a VLOOKUP, but I have been chasing my tail trying to figure out how to implement it.
I work in a computer lab and we have to keep track of how many people sign in (using excel 2010), and how long they were here.
I'm looking for a way to count how many entries were made per hour for every hour someone was signed in.
For example someone signs in at 9:22 am and leaves at 3:34 pm.They were here from 9-10, 10-11, 11-12 etc.. and its getting very tiresome to go through and manually enter a "1" for each individual hour in each cell under the hour.
Is there a way I can feed the in and out times into a spreadsheet and have it automatically count how many people were in the lab each hour?
A
B
C
D
E
1
2
4
2
2
In
Out
8 am
9 am
10 am
[Code] ...........
Is it possible to take data in format dd:hh:mm and convert to working days - ie. 8 hour days?
For example:
02:12:15 (60 hours and 15 minutes .. or 3615 minutes)
to be converted to:
07:04:15 (7 working days: 4 hours: 15 minutes)
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.
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.
I have a form to round to nearest quarter but if it is less than 1 hour I need it to round to a total of 1. Can this be combined in one formula.
I also need my time to be configured so that if the start time is a PM number then end time AM it does not figure right. is there a way to remove the AM/PM from time. I have already tried all the formats from number,time, & custom.
Attached is my form : Timesheet Form 2014.xlsx
I'd like to convert from 24 hour standard time to 12 hour time using VBA code. For example: instead of 13:00 I need 1:00.
View 9 Replies View RelatedI have a list of FLIGHT departure times that are listed in MIL TIME, however, there is no : in the format. Its just 4 or 3 digit numbers. I need to convert these to time in 12-hour clock. If I go to FORMAT/CELL/TIME and select 1:30pm it simply makes the time ZERO!
View 9 Replies View RelatedSee 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 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 Related" =(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 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 RelatedAs 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 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 RelatedBasically 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.
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.
I’m trying to take an existing employee time sheet in Excel (Office XP) that has no formulae whatsoever, and add the appropriate formulae so that all an employee needs to do is enter the daily start and end times and the time sheet will calculate daily, weekly, and overtime hours worked. Among others, some of the problems I’m having are:
I need to keep the original format (though I've added a few columns).
Overtime in the State of Texas does not apply until after 40hrs have been worked. Then any daily hours over 8 can be applied retroactively. So I need a timesheet that shows overtime as regular hours worked until 40 hours have been reached, then separates the daily overtime from the regular column and places it in a daily overtime column. Shouldn't be too hard to find...Right?... Actually, that’s been quite easyexcept for weekends. Saturdays and Sundays are usually overtime but not always.
The real problem is the beginning day of the pay period, if a pay period begins on any day other than Monday (Wednesday, for example,) then weeks one and sometimes three can never equal 40 hours each unless the assumption is that the days worked in the same week but prior or subsequent period are worked at 8 hours each. The formulae must make this assumption. How do I write a formula that assumes an empty cell actually has a value? :o
I know that it’s difficult (if not impossible) to offer any suggestions without seeing the time sheet itself, so, If it would be helpful, and anyone has any suggestions. I’ve uploaded the week one of the timesheet as it stands now.
If you'd like to see the entire worksheet I've uploaded it to ....