Rounding Timesheet To Quarter Hour
Oct 19, 2008
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.
View 6 Replies
ADVERTISEMENT
Aug 1, 2009
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 9 Replies
View Related
Nov 16, 2009
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
View 9 Replies
View Related
Jan 2, 2013
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
View 4 Replies
View Related
Sep 23, 2003
I need to round a time to the nearest quarter hour and have it show in quarters. My formula figures out the hours worked. If the total is 8.36, then it needs to show 8.75. It needs to round up or down to nearest quater hour.
View 9 Replies
View Related
Mar 26, 2014
I work at a facility where we care for adults with disabilities and we need to record times that clients arrive and leave. I created an attendance check-in sheet that needs the current time entered quickly. Is there code that will allow staff to double click on a cell and have the current time entered and rounded to the nearest quarter hour?
View 1 Replies
View Related
Feb 5, 2009
Is it possible to round up or down a 24HR time format.
ie. 18:06 would be 18:00
18:28 would be 18:30?
View 9 Replies
View Related
Oct 30, 2009
I'm trying to figure out how to identify higher earnings in a quarter and return the max of the quarter of time frame and not the value
I'm simplifying a little ...
View 9 Replies
View Related
Oct 7, 2013
How would you calculate the amount of days of a billing period for a consulting engagement by quarter when the engagement could span multiple quarters, be within a quarter or not be in a quarter at all? Project based billing
View 1 Replies
View Related
Sep 4, 2006
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.
View 9 Replies
View Related
Oct 21, 2013
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] ...........
View 3 Replies
View Related
Nov 12, 2013
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)
View 3 Replies
View Related
Dec 2, 2013
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.
View 1 Replies
View Related
Jul 9, 2008
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 9 Replies
View Related
Mar 19, 2008
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 9 Replies
View Related
Apr 4, 2014
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
View 4 Replies
View Related
Mar 7, 2008
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 Related
May 8, 2008
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 12-hour clock. If I go to FORMAT/CELL/TIME and select 1:30pm it simply makes the time ZERO!
View 9 Replies
View Related
Dec 18, 2008
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.
View 10 Replies
View Related
Jan 1, 1970
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 14 Replies
View Related
May 26, 2007
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 9 Replies
View Related
Feb 18, 2010
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 2 Replies
View Related
Aug 3, 2008
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
View 3 Replies
View Related
Nov 25, 2008
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 12 Replies
View Related
Oct 15, 2009
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!!
View 2 Replies
View Related
Dec 19, 2009
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 10 Replies
View Related
Nov 2, 2008
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 Related
Oct 29, 2007
I 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
Apr 12, 2008
" =(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.
View 9 Replies
View Related
Jul 30, 2008
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.
View 34 Replies
View Related
