Apr 08, 2011 Driver Limits. In some countries, there are limits to the hours that truck drivers can work in a string of consecutive days. In this example, the limit is 60 hours, in any period of 7 consecutive days. The maximum hours is entered in cell C1, and the number of consecutive days is entered in cell F1.
Hello, this is probably a bit of a newbie question but I need to work out how many hours off someone has had. I'm after a formula which looks up through the cells and for every 0 it finds it adds 24 to a total. I need this formula to stop when i finds a value that isn't 0 and adds 24hrs-that value to the total. Does that make sense? I've attached the xls file to hopefully show what i'm trying to achieve. Column J shows the end times so and column L shows the amount of time since the person finished work. I hope I'm making sense. Any help would be greatly appreciated. As a side question do you all have degrees in Mathematics? Some of the things i've read are so clever and i'm in total admiration for the intelligence you all have. Thanks in advance of any help. Fess Subscribe for Weekly Excel Tips and TricksHelpful tutorials delivered to your email!Similar Topics
I'm trying to come up with a solution to calculate the man hours and days worked over multiple sheets. I have a file that has 8 sheets. the first seven sheets are the days of the week and the 8th sheet is where I want to calculate the hours. The days of the week consists of 'Name' 'Date' Hours' The 8th sheet consists of 'Name', 'Days of the week in their own column (7 columns in total)', 'Total Hours' and 'Total Days Worked' Now... each day is always a different group and what I'm hoping for is to create a formula that can grab each name with their hours and throw them into the 8th sheet. So when the end of the week is done I have all the Employees weekly hours and days worked all finished. I hope this makes sense. This file is to track about 100 employees that are on a rotational work schedule. I have attached a sample file. Thanks in advance for your help.
Hi, I made a simple worksheet that we have been manually entering the time for employees, but there are too many errors (even with simple math). Can someone help me convert the time of ex: written 8-4:30 or 10-3 (meaning 8:00am to 4:30pm) where you have the total weekly hours? Right now I have a column for overtime. Is there a way to automatically calculate the over time also? The work day is 8-4:30 with a half hour lunch (lunch is not calculated into the hours, so you minus a half hour). 8-4:30 equals 8 hours. So, if a person works 8am to 6:30pm, how can I set it up where in the first row of time, I will type in 8-6:30pm. In the totals column to the far right, it will display 8 hours. In the (O.T.) column, it will display 2 hours. This way, when I call in my payroll, I will have the total 'regular' hours in one row and underneath, I will have the total overtime hours. Or... does anyone have a better solution to keeping track of their hours? Thanks! Marty Good afternoon! I'm working with Excel 2003 sp 3 on Windows xp. I have been banging my head trying to jar loose the formula to calculate man days where 1 equals 8 hours. I have one that was given to me by a coworker: =INT(Q3/8)+(((Q3/8)-(INT(Q3/8)))*0.8) That calculates man days from total hours. I need a formula that will total man days and return the man day answer. Example: weekending 12/25 = 16.2 which is 16 man days and 2 hours weekending 1/1 = 24.7 which is 24 man days and 7 hours Total = 41 man days and 1 hour I have also been asked if there was a way for a number to be entered into a spreadsheet and automatically put in order without a macro or 'pushing a button'. Any advice is greatly appreciated! Happy New Year! ~ Brenda ~
Sorry for the question. Normally I find answers to my excel questions by going through the help tab or by searching on Google. However, I don't even know what question to ask on this one!?! Basically I have created a spreadsheet with several columns, but I have one column that lists the shirt size (YS, YM, YL, AS, AM, AL, XL, 2X, 3X) of each person. Is there a formula that I can create that will tabulate the number of sizes (i.e. AS=2, AM=7, etc.)? In previous years I made a column for each size, and simply placed a '1' in the correct column, and had excel just add the 1's from each column. However, that takes more time and space. I was hoping to streamline it this time around. Thanks for taking the time to read this post. Any help would be appreciated! Thanks, doug I'm trying to figure out what the formula is to multiply the total hours worked per week by their hourly pay for each individual. kind of like the =Sum(I25:I32) Formula but I want to multiply the outcome of that formula by the hourly rate of 11.00, 15.00 and 16.00. Does anybody know how this can be done? If so I would greatly appreciate it. Thanks! charitydc
Hi all, I have a dillemma here, I have to create a schedule which incorporates lunches automatically (either half hour or 1-hour lunches, depending on the circumstances). The schedule only has time in and time out, but I need excel to automatically deduct the lunch break depending on the circumstance: If you worked less than 6 hours = no lunch If you worked more than 6 hours, but less than or equal to 8 hours = 0.5 hour lunch If you worked more than 8 hours = 1 hour lunch I am assuming the formula for this will be really long, but I have looked online everywhere and have not found ONE formula for it. I can't put lunch breaks seperately, so all I have to work with is Time in/out. Also, I wouldn't be able to use military time, so I don't know how excel can assume that time in is in the morning, and time out is in teh evening and/or half-day. If someone can help it would be greatlyyyy appreciated
Entering time values in custom format [h]:mm:ss Cells accept hours over 23, Adding cells in column returns correct total time. Have not found a way to multiply these cells by a $ hourly rate. So use HOUR(cell ref) and MINUTE(cell ref) to capture values in referenced cell - then use these values to calculate total payment for $rate per hour. However, the HOUR(cell ref) formula returns the hours in excess of 24 when the cell contains an hour value in excess of 23 (ie 27 hours returns 3).
Hello, Please help!!! We have a client who will pay using the 1/4, 1/2/ and 3/4 of an hour increments as follows: From the hour mark to 6 minutes = 0 time paid Over 7 minutes to 15 minutes = 1/4 hour 15 16 minutes to 30 minutes = 1/2 hour 30 30 minutes to 45 minutes = 3/4 hour 45 46 minutes to 60 minutes = one hour. They work on shifts and the operation is 24 hours. Each gets 30 minutes for breaks which is not paid. Pay is biweekly and overtime is anything over 40 hours. Currently, the template we are using looks like this (sorry, I could not figure out how to paste it so you can see it in Excel layout): WEEK 1 Sunday Monday Tuesday Wednesday Thursday Friday Saturday 5/16 5/17 5/18 5/19 5/20 5/21 5/22 TIME IN ENTER TIME, INCLUDE AM OR PM TIME OUT TIME IN TIME OUT TIME IN TIME OUT TIME IN Total Hours First Week TIME OUT HOURS WORKED-OR- choose one *HOLIDAY WORKED PAID HOLIDAY (not worked) *OTHER PAID HOURS EXPLANATION OF *HOLIDAY WORKED HRS OR *OTHER I am very new to Excel and need help creating a formula that will allow us to calculate this timesheet? I am in desperate need for help. Thanks..
I have a sheet which has the start, stop and time taken for lunch breaks. I want to calculate the total time worked such that i have the hours worked. So if i worked 7 hours and 30min then the total time would be 7.5hrs. But i am not sure how to take into account the lunch break time (see enclosed sheet). anyone have any ideas?
I need a formula to calculate overtime, after a work week of 40 hours. for example: if an employee works 10 hours a day we would not count towards overtime until the employee completes a 40 hour work week. Any suggestions would be greatly appreciated!!!!! Thanks, YV I am trying to calculate time based off a non-conventional quarter hour time system example; 8:00 to 8:07 = 8.0 hrs 8:08 to 8:23 = 8.25 hrs 8:24 to 8:38 = 8.5 hrs 8:39 to 8:53 = 8.75 hrs 8:53 to 8:59 = 9.0 hrs I am having trouble writing an equation that would sum the clock-in and clock-out times (2 per day) and display the total time in these quarter hours and adding an additional hour if it is >= to *:53. Any help would be great. We have an Excel spreadsheet that sits on the network. People need to open the file to be able to sign up for various duty rosters. We would like for the file to open for the first person. And then for any others after that, get a message that the file is in use WITHOUT the option to open a read-only copy. Our staff can't read and they keep opening additional copies of the file! I have read about sharing the file and I don't think that would make things any better. Thanks, Lynn
Hello everyone, I am new to this forum and have been using excel for about 3 years. I have learned alot but still can not figure out how to make numbers add up in one box like a running total. Meaning, If I have a list and in the list is one cell for each item say like cell a1 is for a can of coke and cell a2 is for a car tire and so on,and i want to keep a running total by adding a number to cell a1 or a2 like say '3 i.e. 3 tires or cans of coke' and later that day i come back and need to add 2 more ......so i want to be able to just click on the cell a1 or a2 and enter the number 2 and the cell would add the number in it to the number im adding to it to show a result of 5 tires or whatever. I am sorry If i am not asking the question in an understandable manor but its the best way I can think to ask it.lol anyways, any help would be appreciated, Thanks in advance. James
I am attempting to figure out how to change the formula to automatically subtract a 30 minute lunch if the total time for the day exceeds 6 hours worked. B value Time of day punch in: 9:30 AM C value =IF(D11=',','-') D value Time of Day punch out: 6:00 PM E value =IF(D11=',',IF(D11
Hi all, I've had a long search through your pages to see if this question has been answered before but having browsed through about 50 pages worth of threads I couldn't see anything, but if I am repeating prior information I do apologise. I've written a macro that is relatively simple. It just takes some information in one format, rearranges it, adds some formatting and performs some calculations. Nothing incredibly fancy but it works fine on my computer. Now, I need to share this macro with some other people, so basically I've just sent that excel file on to the people that need to use it. Should be fine and in most cases it is, however there is one user who although they can open the file, can't seem to get the macro to run properly. It seems to get a small way through the macro but then stop with no error messages or any sign that it hasn't completed properly. I have checked Macro Security level and that is the same as mine, Tools - Add-Ins is the same, In Visual Basic, Tools - References is the same as mine. It is the same Operating system and the same version of Excel. I have even signed into this person's computer as myself (it's a big company network thing) and tried to run the macro and it works fine, so there is nothing wrong with the hardware. I've googled and searched and tried everything I can think of but I'm no closer to solving this problem, so if anyone has read through this wall of text and can come up with a possible solution, that would be greatly appreciated to save me from tearing ALL my hair out! Thanks very much for your time. Hi there Please can someone help me. How do I create an IF formula to find cells where the number of hours in one cell is more than the number of hours in another cell? eg one cell has 0.40 hrs and another has 02.25 hours - I want have an IF formula to say where 02.25hrs > 0.40 then type 'FAILED' but I cant overcome the greater than problem on the hours which is giving me the wrong answer.
How do I calculate the number of batches per hour? Here's what I have so far: Cell B5: start time: 4:15 formatted as 4:15:00 am Cell B6: finish time: 6:15 formatted as 6:15:00 am Cell B7: total time: 2:00 formula in cell: =TEXT(B6-B5,'h:mm') Cell B8: total batches processed: 22 (this is entered manually) Cell B9: batches per hour: formula in cell: =B8/TEXT(B7,'h') as long as I have this formula in cell B9 the answer comes out correct which should be 11 per hour. If the formula in B9 is B8/B7, the answer is 264.0, Is this because of the way excel is reading the total time or the total time is formatted as a time, not an actual number? Is this the correct way to solve the problem? answer should be 11 per hour. I am trying to calculate the average Talk Time into Minutes and Seconds. Every time I have tried, I get the wrong average. For example, if I have a total of 53 calls and the total talk time is 4:19:05, what would the average talk per call be? I tried doing everything in seconds (this would give me a total of 15545 seconds) but the total I was getting is 4:89. What would the formula be to get the correct average time? hi guys, i have a simple spreadsheet where i'm totalling a few values thru a formula. it's similar to this: =D13*F13 i'm trying to create a funcion that says if d13 * f13 >= 300, i want the value to stay at 300 max and at the same time, if d13 * f13 < 300, i want the total to remain whatever it is. i have been able to create both of these formulas successfully by themselves but i can't combine them. is this possible? any help would be greatly appreciated!! I'm trying to use conditional formatting to highlight phone calls that came in between certain hours. The call times are in the custom format h:mm, although it could easily be changed to an Excel time format. I'm using Excel 2002. I want the cell to have a different fill color if it falls within particular time frames. Example: If the call came in between 22:00 and 23:59 color is light green. If the call came in between 23:59 and 08:00 the color is yellow. Example spreadsheet is attached. Thanks in advance. Today I ran into an odd problem. I typed in values for column A rows 1 through 10 then values for column B rows 1 through 10. then in column C, I made the formula C1=A1/B1. The math was correct it showed 542 in the C1 cell. So I dragged that formula down and it showed 542 in all column C cells which is not correct. And when I went to check to see if the formula was correctly dragged it was. For instance, the formula in C2 is =A2/B2 however the value of that cell showed 542 which was not the correct math/value. But it gets even more weird. When I click on the Column C cells and then it shows the formula up top in the formula bar and if I put my cursor anywhere in the formula bar and hit Return the formula does not change however the correct value then appears in the Column C cell. It is like the act of putting the cursor in the formula activates it to work properly but until it is activated that C cell shows the value of the cell which it was dragged down from. This is quite bizarre. Has anyone ever seen this before? I have no idea what is going on. I ran a scan for viruses and none were found. I tried it on several new/different spreadsheets but it keeps happening. Thanks for any tips on this.
I've been poking around trying to figure this out but can't. The time of 9 hours, 15 minutes is being returned from a calculation but I want the cell to display the 9.25 hours instead. This seems like something simple on the surface, but apparently it's not. Any help would be appreciated! I need help desparately. Last night I spent several hours working on an Excel 2002 spreadsheet and it has disappeared. I saved it routinely as I was making many changes updates etc. I opened original file --stuff.xls --- made changes and saved it as again as stuff.xls. I have done this several times before. Today, when I go to open stuff.xls, it is nowhere to be found!!!!! I have done a search, for any file modified since Thurs and nothing has appeared. I have checked the recycle bin, this did not go into auto recover nothing of the like. I am just traumatized, because I have NEVER had anything like this happen before. I even thought perhaps I didn't save it as often as I thought, (I know that I did) but I remembered that I saved it at least once and I can't even find an Excel file that has been modified since Thurs!!! Does anyone have any idea what could have happened to this file?? BTW, I have prayed, rebooted, run spybot, called witch doctor, all with no success. I have never had a file to just disappear with no trace. Any help would be greatly appreciated. Thanks in advance--- I am trying to figure out how to write a formula to figure out production cycle time. We will be building 8 'widgets' a day. We will work 10 hours a day. There is a 20 minute break at 9:20 am. Production shuts down for lunch 30 minutes for lunch at 12:30. Production starts at 6:00 am. Here is what I can do. Production cycle time = (10*60)-30/8 or 71.25 minutes If we start at 6:00 am, the first scheduled cycle completion time = TIME(6,0,0+71.25/1440) My problem is when I am trying to use a logical 'IF' statement to account for the 20 minute break or lunch and still calculate the end of each cycle time through the day I receive a number of error messages. This is the formula I am trying to make work. I have the cell the formula is in, formatted with a 'mm,ss' format. =(IF(D4+G1/1440>9:20,(D4+G1)/24,(D4+G1+15)/1440)) I have included an attachment to help (a picture is worth a thousand words). Any help will be greatly appreciated. I have bought a number of books in an attempt to figure this out, and I am still stumped
Workbook A has a cell that gets data from workbook B as an external link. It does this using the indirect function because it needs to concatenate the path and filename from other cells. Problem is that if workbook B is closed, the cell in workbook A shows #REF!. So can indirect work using closed external files or must the external files be opened? Is there a clever way of making that work? Thanks! |
Download free Timesheet Templates for Microsoft® Excel®Download timesheets and other payroll-related templates for Excel, OpenOffice, and Google Sheets. The Vertex42 timesheets and timecards contain multiple versions within a single workbook, allowing you to choose weekly or bi-weekly, or different methods for entering times (decimal vs.
Hh:mm format). In addition to the time sheets, check out the payroll register and vacation accrual templates. Together, these tools provide a very simple and budget-friendly solution for small businesses.