Rounding Timesheet To Quarter Hour
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),((C13B13)*24)+24,(C13B13)*G1424))+IF((OR(E13="",F13="")),0,IF((F13<E13),((F13E13)*24)+24,(F13E13)*24)),2)
I Have also attached the file so you can see it completely.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Rounding To The Neartest Quarter Hour
I have searched around and have found several formulas designed to round to the nearest 15 min interval, but none seem to be working for me. If anyone has some insight. A1 A2 R1 Main Task Total in Hours R2 Sub Tasking 1 30 min R3 Sub Tasking 2 6 min R4 Sub Tasking 3 5 min R5 Sub Tasking 4 23 min What I need to happen is for the total of the time allotted for each tasking to be added and rounded to the nearest 15 min interval in a hour.min format. For instance, the above would be rounded to 1.25 71 mins total, rounded to 1.25 because .25 is one quarter of an hour in decimal form. I need it to be this way to be able to multiply that by a charge amount for a total cost for the Main Task. The numbers in A2 will all be whole integers.
View Replies!
View Related
24 Hour Timesheet
I am having trouble with this formula =IF(E4D4 < 1/24*7.1,E4D4,E4D41/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
View Replies!
View Related
Changing Date/time To Run A 12 Hour Production Schedule, And Not 24 Hour
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 readjusts 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.
View Replies!
View Related
Banker's Rounding Or Scientific Rounding
I am trying to round similar to Banker's Rounding or Scientific Rounding but I can't find a consistent formula that works perfect with decimals. Using three decimal places for all the samples, I can get 0.0785 to round to 0.078 but 0.1785 wants to round to 0.179 instead of staying 0.078. Or 0.0005 will round to 0 but 0.5115 wants to round to 0.511 instead of 0.512. Here is a list of sample numbers along with desired results: .0785 should be .078 .5115 should be .512 .5035 should be .504 .0005 should be 0 .0025 should be .002 .0194 should be .019 .0195 should be .02 .0135 should be .014 .0115 should be .012 .8115 should be .812 I cannot find a formula which gives me all of these results. Here is a list of the formulas I have tried so far (NOTE: cell A2 is the working cell in my worksheet where I enter the number to be rounded) 1) =MROUND(A2,0.001) 3) =ROUND(A2,3) 4) =IF(ISERROR(IF(MOD(MID(A2,4,1),2)=1,CEILING(A2,0.001),FLOOR(A2,0.001))),0,IF(MOD(MID(A2,4,1),2)=1,CEILING(A2,0.001),FLOO R(A2,0.001))) 5) =EVEN(A2) 6) =ROUNDUP(A2,3) 7) =ROUNDDOWN(A2,3)
View Replies!
View Related
CONVERT 24Hour Time To 12Hour Time
I 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 12hour clock. If I go to FORMAT/CELL/TIME and select 1:30pm it simply makes the time ZERO!
View Replies!
View Related
Timesheet #2
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.
View Replies!
View Related
Timesheet For 24/7 Worktime
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).
View Replies!
View Related
Creating A Timesheet ....
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"...
View Replies!
View Related
Timesheet Calculator
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 VLookup and If functions. Is this the best route to take ? All is explained within the workbook.
View Replies!
View Related
Determining Quarter And Year
In cell A1 I have a date entered as text as "Apr 2007". (That's the way my tool pulls it. Format can be changed if it helps) I was able to pull the Quarter and year (Q2 2007) using... A2 ="Q" & ROUNDUP(MONTH(A1)/3,0)&" "&YEAR(A1) I need to pull the next three quarters and their year. (Q3 2007, Q4 2007, Q1 2008)
View Replies!
View Related
End Of Quarter Function
Need function to calculate the end of quarter for a given date after adding any number of months to that date. Example cell A1 would contain 01/15/2010. I want to add 6 months to A1. A2 would contain 6 making it 07/15/2010, B1 would contain a function that would display 09/30/2010.
View Replies!
View Related
Overtime Calculation On Timesheet
" =(C2 >D2)*MEDIAN(0,D21/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 (06001800) standard rate, day (06001800) overtime rate, night (18000600) standard rate, night (18000600) 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 972003 format.
View Replies!
View Related
Timesheet For 2007 Formula
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.
View Replies!
View Related
Work Schedule Timesheet
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.
View Replies!
View Related
Employee Timesheet With Overtime
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 Replies!
View Related
Timesheet WantedIF Blank
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...
View Replies!
View Related
Subtracting Times :: Timesheet
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, =J58LOOKUP(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.
View Replies!
View Related
Counting Hours On A Timesheet
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 2cell1) 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!!
View Replies!
View Related
Timesheet Nested Function
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 Replies!
View Related
Calculate The Quarter To Date Number
I was given the formula below to calculate the quarter to date number. The months from 1/1/05 thru 12/31/07 are in cells C3AL3. First  can anyone explain the formula so I may understand it and possibly modify it for other uses Second  wherever the formula has 3:3 and I copy it to the next cell below it, I have to change the 4:4 to a 3:3  any suggestions on how to do this easily. By the way, the formulas are in column AO, staring on row 4. =SUM(OFFSET(A3,1,MATCH(DATE(YEAR(Date!$C$6),INT((MONTH(Date!$C$6)+2)/3)*32,1),3:3,0)1,1,MATCH(Date!$C$6,3:3,0)MATCH(DATE(YEAR(Date!$C$6),INT((MONTH(Date!$C$6)+2)/3)*32,1),3:3,0)+1))
View Replies!
View Related
How To Calculate Max Profit Per Quarter
Formula to get the Quarter number for the highest profit per Product, like in B16:F16. The excersiser instracted us that it must be with one formula with no Macro, not User Defined Function and not openning additional colums. I know how to find the qtr. Number  like for A2: =INT((MONTH(A2)+2)/3) but I am lost in finding how to combine the calculation for the Qtr and the most profitable Qtr. probably with MAX on SUM.
View Replies!
View Related
Constant Line In Chart By Quarter
I have a bar chart that has data by the quarter over a span of 3 years, so there are a total of 12 bars on the chart. Everything looks good there, but now I need to add the minimum amount that the customer needs to acheive (which varies every quarter) in the form of a line. So I added a line chart with the minimum data, but the problem is it goes up and down from point to point and doesn't create a visual horizontal line for the customer to see if they are hitting their minimum every quarter. Is there any way around this?
View Replies!
View Related
Average Number Of Days In A Quarter
I have a worksheet containing 5 columns. A start and end date, a number of days between these dates, the quarter number of the start date (i.e. 1 to 4) and then the year. I want to calculate the average number of days per quarter so an average of days in column C, based on the details in column D and E.
View Replies!
View Related
Identify Top Quarter Of Data
I have been easily identifying the bottom quarter of data using =IF(G2="","",IF(RANK(G2,G$2:G$100,1)<=INT( COUNTA(G$2:G$100)/4),"Yes","")) Now I need the top quarter and am not sure how to change the formula. I have tried changing <= to >= but I then get the top 75% rather than 25%. I have tried adding +50% and +.5 ... =IF(G2="","",IF(RANK(G2,G$2:G$100,1)<=INT((COUNTA(G$2:G$100)/4+50%)),"Yes",""))
View Replies!
View Related
Finding The Quarter Period Of A Date
I'm trying to find out the date quarter of a given date in cell A1.ie Q1,Q2 etc. I've found the code below to work out fiscal quarters.I thought if I dropped the +1 from the end of the formula it would work out the quarter in non fiscal terms. This however only works from months Jan to Nov. December returns a zero. =MOD(INT(MONTH(A1)/4)+1,4)+1
View Replies!
View Related
Identify Bottom Quarter Of Data
I have a column of data that comes from simple division formulas, E2/F2. I am wanting to take the Percentages given and quickly recognize the ones in the bottom quarter. 100% 75% 23% 39% 50% N/A 100% 5% 8 items  the lowest 2 (the bottom quarter) should "stand out"  5% and 23% I figured two options: 1.) Conditional Formatting  don't know what the formula would be to use 2.) If statement  preferred method. I would like in the cell next to the percentages for there to be a blank cell if the amount is not in the bottom 25% and if it is I would like the cell to read "Yes" I thought something along the lines of =if(G2 in range(G2:G100)is bottom(.25),"Yes","") Of course that is not real code. I'm hoping someone will be able to edit that to make it functional. Or guide me in the right direction. And you may have noticed the "N/A" in the list above. I would like excel to view this as 100%, at the least just ignore it and don't give me an error.
View Replies!
View Related
Overtime In Semi Monthly Timesheet
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 ....
View Replies!
View Related
Finds The Matching Date In The Timesheet
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.
View Replies!
View Related
Calculating Time, Timesheet Calculation
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)))..................
View Replies!
View Related
Create A Timesheet With Time Formulas
I am trying to create a timeline spreadsheet for a weekly radio show I produce. We have 3 segments and the total time of the 3 must add to 59 minutes. Within each segments there are numerous variables with a certain time value. I am trying to figure out how to have time reduced correctly. For Example Segment 1 Story A 5:00 minutes Story B 4:30 minutes Story C 3:00 minutes What I need is Segment Total (A+B+C) in one cell and Remaining time (from total 59 minutes) in another. I know this may seem silly for most of you, but I cannot get the cells to format properly at all.
View Replies!
View Related
Formula To Return The AVERAGE By Calendar Quarter
I'm trying to write a formula to return the AVERAGE by calendar quarter, IF the quarter has ended. For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10. But, if Mar was 0 (only meaning no activity) I still need it to calculate. The formula I started with is  =IF(A1=0,0,AVERAGE(A1:A3) This works only if cells A1:A3 have a value greater than 0. How can I write the formula so that as long as A3 is not blank that it will return the AVERAGE?
View Replies!
View Related
Aligning Rows: Line Up Accounts Across Each Quarter
I run reports from Accounting systems that reflect different periods. Of course each period ends up having more or less accounts. See the attached file. This is just one little sample of big massive reports. I need to line up accounts across each Quarter. so that i can calculate differences between each quarter. In the past few times, I have done this manually. There has got to be a way to do this quicker. I have tried Pivot table. but pivot table didn't help either (unless i was not doing it right).
View Replies!
View Related
VLOOKUP From Main Workbook To An Employee Timesheet
I have a timesheet worksheet  and the employees fill it in against project numbers. The cell to the right of each "item" has the total number of hours spent on that project. However, during the week, we could jump to and from multiple projects. My query is, can I do a VLOOKUP from my main workbook to an employee timesheet, search for a particular project number in column C, and then it would pick up the corresponding number (of hours) in column L. Basically a formula which searches for a value (in column C), and then returns the value in the adjacent row of a different column (column L). Furthermore, it would be even better if the formula found ALL the same project numbers within the weekly worksheet, and did a sum of all hours in the adjacent cell of column L.
View Replies!
View Related
Summary Timesheet Report From 52 Weekly Sheets
i have daily time sheets that make up a week and have 52 sheets for each week...there are contract numbers and contract ticket numbers that i want to use as criteria to sum the total hours of each day and export the data to a sheet that will keep a running total of all hours booked to those contract number and contract ticket number over the coarse of the year as i fill out the weekly time sheets.
View Replies!
View Related
Lookup Function (view Data For Any Quarter Of Any Year)
I have spreadsheet that I use to display quarterly metrics. Within the workbook, there are two spreadsheets; (1) output (2) data. Part 1: I want the ability to view data for any quarter of any year. In columns OP, I have created a dropdown for the year I want to view and option buttons for the quarter I want to view. The data is organized in one sheet and includes all information by quarter. As an example, say I want to display data for District 112 and I want to display data for the 3rd Quarter of 2007. I am trying to create a formula that would look in the table as follows: (1) look for district 112 then (2) look for 2007 then (3) look for 3rd quarter then (4) look for units sold. I tried using a modified lookup function but didn’t have any luck. Part 2: The second part of my question is similar to the first part but in this case I need to summarize data (cells $B$18:$D:$22). As an example, say I want to display the total number of sales in the U.S. for the 3rd quarter of 2007. In this example, I would look in the table for the following: (1) look for Division then (2) look for 2007 then (3) look for 3rd quarter then (4) sum all based on criteria.
View Replies!
View Related
Timesheet: Calculate Time Within A Period When Crossing Midnight
I am creating a timesheet using excel 2003 users enter their shift start/finish time and a break start/finish time. Emplyee's can work night shifts (ie across midnight). There are penalty rates which apply at different times. I need to be able to work out the amount of worked time that fits into a certain time period. eg. 10pm7.30am, 7.30am10pm. I have a solution based on A clever formula from Daniel Maher that will calculate time within a period. But it doesn't work when the shift goes over two days. I have attached a spreadsheet to help show the problem .......
View Replies!
View Related
Timesheet: Send Out A Mail Template Everyweek To All The Department
I send out a mail template everyweek to all the dept. employees using outlook. I attach a excel sheet to the mail so the employees can type in their hours and then sent it back. Then i go in manually each sheet and copy the info in THE timesheet excel sheet and sent it to the HR. Any easy way automate this process? What id like is to send a mai lcontaining the excel sheet, the emlpoyees fill it and the data tranfers to a excel sheet...
View Replies!
View Related
Combo & List Box To Set Calendar Quarter
I need to define a specific calendar quarter and year, and want to do so using a combo box for the quarter (e.g. 1st Quarter, 2nd Quarter, 3rd Quarter, 4th Quarter) and a list box for the year (so the user can select any year desired). The selections in each item should then somehow define the quarter's date range so I can use it in my SUMPRODUCT calculations. Example: User selects '1st Quarter' from the Combo Box User selects '2004' from the List Box CurrentQuarter range is somehow defined to equal 1/1/04 through 3/31/04. If this isn't possible, then perhaps two ranges can be defined based on the user's selections: QtrStart is set to 1/1/04 QtrEnd is set to 3/31/04
View Replies!
View Related
Runtime Error 1004 In Company Employee Timesheet Spreadsheet
we've been using this spreadsheet as a timesheet since May of 2002. Recently in the past couple of days when we try to update an employee's time and date of work done by clicking on an update button on the first Worksheet, an error (Runtime Error '1004' Method 'Range' of object '_Global' failed) pops up. Clicking the 'Debug' button opens a window up with this information:
View Replies!
View Related
IF Statement To Display Quarter Of A Year Based On A Threeletter Month Abbreviation
I'm trying to wrtie an IF statement to display one of Q1, Q2, Q3 or Q4 based on the three letter month abbrev. All i have so far is the following, which isn't leading anywhere  and i have a feeling theres an easier way to write it. =IF(((OR(G2="Jan","Feb","Mar"),)),"Q1",""),IF(OR(G2="Apr","May","Jun"),"Q2","")
View Replies!
View Related
