How do you make a total hour calculation?
Martin Borcher wrote on June 23, 2005 07:31 EST |
Hey,
How do you calculate the total number of hours you have worked - in maybe lets say a week?
When I use the function “=sum()” or “+” I get the wrong number of hours because it starts counting over again after 24h.
Fx. 8h+10h+10h=4h
All help welcome at cabo04ae@student.cbs.dk
ASP
Martin Borcher
|
Display sum of hours when > 24h
xladdict wrote on June 23, 2005 08:32 EST |
Hi Martin
Change your cell formatting from e.g. hh:mm to [h]:mm - this will force Excel to display 46:23 instead of 22:23. |
Good examples to create time sheet
michael A. wrote on July 09, 2005 20:56 EST |
Thanks you for every thing. Now I can keep in trak on my time card...
|
Calculating wages on weekly hours
Amanda wrote on July 12, 2005 07:37 EST |
Hi I would like to total the amount that is due at the the end of the week based on hourly rate, the problem is it does not seem to acknowledge the minutes on the total, can you help?
Reply to amanda.parga@slam.nhs.uk |
Reply: Amanda
Alan wrote on July 13, 2005 20:57 EST |
Hi Amanda,
[QUOTE=Amanda]Hi I would like to total the amount that is due at the the end of the week based on hourly rate, the problem is it does not seem to acknowledge the minutes on the total, can you help?
Reply to [email]amanda.parga@slam.nhs.uk[/email][/QUOTE]If thatis correct, then either the minutes were not entered, or more likely, the numbers were rounded (perhaps with a formula) somewhere.
Check the formulae carefully and it should become obvious hopefully.
Alan. |
How to take break times into account?
Seano wrote on July 27, 2005 02:13 EST |
I'd like to be able to subtract time out of the daily hours for lunch. Ideally by adding another column between time in and time out to keep sheet format logical. Unfortunately the only formula arrangement I can think of based on above is incredibly cumbersome....
Any ideas? |
keeping time as number not text to sum
Jennifer wrote on December 31, 1969 19:00 EST |
| I've calculated the difference in h:mm by using the TEXT(A1-B1,"h:mm") but I need the result to remain as a Number so I can then sum hours for a monthly total. |
rona wrote on August 09, 2005 00:45 EST |
| i have a problem on creating our company's timesheet most specifically on rounding. Based on company policy, overtime should be more than 30 minutes and it should be rounded to 15 suceeding minutes, but if for example you have 44 minutes overtime, it is still considered 30 minutes. What formula can help me to solve my problem. Any idea? Please help me. Thank you! |
Reply: Seano
Alan wrote on August 09, 2005 02:42 EST |
Hi Seano,
[QUOTE=Seano]I'd like to be able to subtract time out of the daily hours for lunch. Ideally by adding another column between time in and time out to keep sheet format logical. Unfortunately the only formula arrangement I can think of based on above is incredibly cumbersome....
Any ideas?[/QUOTE]Just do something like:
-(TimeIn1-TimeOut1)-(TimeIn2-TimeOut2)
HTH,
Alan. |
Help with after midnight timesheet entries
Lee Ann wrote on December 31, 1969 19:00 EST |
I need to create a daily timesheet with the start time @ 6pm the end time after midnight sometimes. I also need this sheet to deduct lunch breaks. I can get the formulas for the times before midnight, but not after. I know the basics of excel but not overly experienced with it. Can you help me please, this is starting to make me nuts.
|
Help with after midnight timesheet entries
Lee Ann wrote on December 31, 1969 19:00 EST |
I need to create a daily timesheet with the start time @ 6pm the end time after midnight sometimes. I also need this sheet to deduct lunch breaks. I can get the formulas for the times before midnight, but not after. I know the basics of excel but not overly experienced with it. Can you help me please, this is starting to make me nuts.
|
Converting
Chuck wrote on December 31, 1969 19:00 EST |
Hi
I would like to run my spreadsheet in actual hourly time. e.g. 4:30 as opposed to 1630. What formula can I use to convert the cells to acutal time and then total the columns correctly? Right now when I add 7.30 and 7.30 or seven hours thirty minutes, I get 14.60. and I need it to say 15 hours.
Any help? |
Different Time Sheet
WilliamVierra wrote on November 10, 2005 10:50 EST |
I did this a slightly different way which seems to produce better results and lets you account for a lunch break.
I use two rows for each day the formula in D6 is =(C6-B6), the formula in D7 is =C7-B7. The formula in E7 is =D6+D7. The formula in E16 is =SUM(E7:E15). The formula in F16 is =E16*24.
Column A6 and A7 have the date in them Column A8 to A15 have the formula =A6+1, =A7+1, =A8+1, =A9+1, =A10+1, =A11+1, =A12+1 and =A13+1.
I have attached an example. |
My favorite timesheet template
Gary L Brown wrote on November 11, 2005 09:09 EST |
My favorite timesheet has 9 columns A:I.
Col A: [B]Wk# [/B] =WEEKNUM(C3)
Col B: [B]Day[/B] =CHOOSE(MOD(C3,7)
+1,"Sat","Sun","Mon","Tue","Wed","Thu","Fri")
Col C: [B]Date [/B] =IF(OR(MOD(C3,7)<2,MOD(C3,7)=6),C3+CHOOSE(MOD(C3,7)+1,2,1,0,6,5,4,3),C3+1)
Col D: [B]Start [/B] [direct input] 7:48:00 AM
Col E: [B]End [/B] [direct input] 5:45:00 PM
Col F: [B]Lunch [/B] [direct input] .75
Col G: [B]Worked Time[/B] =(MOD((TIME(HOUR(E3),MROUND(MINUTE(E3),15),0)-TIME(HOUR(D3),MROUND(MINUTE(D3),15),0)),1)*24)-F3
Col H: [B]Cum Hrs/Wk[/B] =IF(A3<>A4,SUMIF(A:A,A3,G:G),"")
Col I: [B]Notes: [/B] [direct input] Any notes to explain variances in schedule
This format rounds to the [B]nearest 15 minutes [/B] [Col G] and runs Monday thru Friday [Col C]. If you work Saturday or Sunday, simply entering the correct date in Col C (overriding the formula) will adjust the Wk # and Day.
This format also [B]takes working past midnight into account[/B].
It does NOT allow for working more than 24 hours in a shift.
Row 2 typically needs to have Col A and Col C as direct input. From row 3 and down, the formulas handle all info in Col A, B, C, G, H.
Hope this thread helps.
Sincerely,
Gary Brown
[email]gary_brown@ge_NOSPAM.com[/email] |
My favorite timesheet template
Gary L Brown wrote on November 11, 2005 09:20 EST |
The topic "My favorite timesheet template" was my first post. If you see /B instead of Bold print, please ignore it.
Thanks,
Gary Brown |
timestamp
owl527 wrote on November 11, 2005 23:39 EST |
| I have a spreadsheet contain a sequence of procedures and I need timestamp for each task being done. how do you get a timestamp at a specific field? currently I am typing the time in, is there a faster way to do it? |
timestamp
D. Mckeever wrote on December 31, 1969 19:00 EST |
[QUOTE=Owl527]I have a spreadsheet contain a sequence of procedures and I need timestamp for each task being done. how do you get a timestamp at a specific field? currently I am typing the time in, is there a faster way to do it?
Try this... Format the timestamp cells for what you need; i.e. date & time, just the time. Then just use the quick key combo CTRL + ; (semi-colon)
Dave B. McKeever |
Reply: owl527
Alan wrote on November 13, 2005 21:39 EST |
Hi owl527,
[QUOTE=owl527]I have a spreadsheet contain a sequence of procedures and I need timestamp for each task being done. how do you get a timestamp at a specific field? currently I am typing the time in, is there a faster way to do it?[/QUOTE]Not sure if this is what you mean, but if you select a cell where you want the current time then press Shift-Ctrl-: (colon).
HTH,
Alan. |
AMurderOfCrows wrote on January 03, 2006 02:03 EST |
Hey all
Needing a timesheet to handle multiple employees with time entered as follows:
830a
945p
1252a
It needs to be able to deduct lunch breaks and caclulate overtime based on a 40 hour work week that starts on a Sunday and ends on a Saturday/Sunday crossover (so if you started your shift on saturday and ended it after midnight, the hours after midnight still count correctly as a Saturday shift).
it ALSO needs to be able to handle pay periods starting on the 1st and on the 16th of each month (this won't effect anything on the timesheet itself, but will effect the Pay calculator i'm trying to tie into it).
I found a great starting point here:
[url]http://www.cpearson.com/excel/overtime.htm[/url]
the problem is that i can't come up with a good way to set the conditions of overtime.
Conditions:
Workweek starts on Sunday Morning (generally around 8am, but can start anytime sunday morning).
Workweek ends on Saturday Night/Sunday Early morning (generally around 3am, but can go a lil bit later sometimes).
Regular Workweek consists of 40 hours. anything over is overtime.
Certain holidays are paid, but don't count for overtime. the Holiday pays for 8 hours Regular Rate (standard shift) if you do not work. The holiday pays the amount of time worked at Double Rate if you work that day. If the holiday falls into overtime, you get Double and a half
ANy help? |
AMOC wrote on December 31, 1969 19:00 EST |
Hey all
Needing a timesheet to handle multiple employees with time entered as follows:
830a
945p
1252a
It needs to be able to deduct lunch breaks and caclulate overtime based on a 40 hour work week that starts on a Sunday and ends on a Saturday/Sunday crossover (so if you started your shift on saturday and ended it after midnight, the hours after midnight still count correctly as a Saturday shift).
it ALSO needs to be able to handle pay periods starting on the 1st and on the 16th of each month (this won't effect anything on the timesheet itself, but will effect the Pay calculator i'm trying to tie into it).
I found a great starting point here:
http://www.cpearson.com/excel/overtime.htm
the problem is that i can't come up with a good way to set the conditions of overtime.
Conditions:
Workweek starts on Sunday Morning (generally around 8am, but can start anytime sunday morning).
Workweek ends on Saturday Night/Sunday Early morning (generally around 3am, but can go a lil bit later sometimes).
Regular Workweek consists of 40 hours. anything over is overtime.
Certain holidays are paid, but don't count for overtime. the Holiday pays for 8 hours Regular Rate (standard shift) if you do not work. The holiday pays the amount of time worked at Double Rate if you work that day. If the holiday falls into overtime, you get Double and a half
ANy help? |
search a particular item thoughout worksheet
Kate wrote on December 31, 1969 19:00 EST |
then give me the total hours worked for that projects.
I work for a Arch/Contruction company, each job has it's own project number and name, but it the timesheet there's a breakdown of descriptions, I need grand total throughout timesheet.
Thanks
Kate |
Help with Formula for OT on 40 hrs workweek
ijgoesnar wrote on March 10, 2006 14:35 EST |
Hi, I am creating Timesheet spreadsheet, I can't figure out the formula to calculate overtime if a person work 7 days in a row (over 40 hours). on the 6th & 7th days, it should be Overtime.
Help |
timesheet
Gary L Brown wrote on March 10, 2006 14:52 EST |
use my posting from 11-Nov-2005. The formula in Column C calculates weekdays only (Mon, Tues, Wed, etc). When working a weekend, overwrite the formula.
HTH,
Gary Brown |
Spreadsheet with actual hourly time
larrynjr wrote on April 09, 2006 11:51 EST |
[QUOTE=Chuck]Hi
I would like to run my spreadsheet in actual hourly time. e.g. 4:30 as opposed to 1630. What formula can I use to convert the cells to acutal time and then total the columns correctly? Right now when I add 7.30 and 7.30 or seven hours thirty minutes, I get 14.60. and I need it to say 15 hours.
Any help?[/QUOTE]
Chuck,
Good Day! Here's what we are using : E
A B C D HOURS WORKED
IN OUT IN OUT
Larry 7:00 AM 12:00 PM 1:00 PM 4:00 PM 8.00
Jimmy 7:30 AM 12:00 PM 1:00 PM 4:00 PM 7.50
-----
TOTAL 15.50
Type time with a space followed by A or P after the time then the formula for hours work:
=(D-C-INT(D-C))*24+(B-A-INT(B-A))*24 then Format column E as Number with 1 or 2 decimal places.
Note: Lunch time from 12PM - 1PM is not included. The computed hours is regular time. You can apply the same for overtime hours. If time is 8:45 PM
type 8:45 then space then P
You can add the time of Jimmy and Larry using the Summation icon
I maybe too late for this response, but i shall appreciate sharing with me the responses you receive. Thanks.
Regards,
larrynjr. |
rodyzamora wrote on April 13, 2006 04:12 EST |
[QUOTE=Jennifer]I've calculated the difference in h:mm by using the TEXT(A1-B1,"h:mm") but I need the result to remain as a Number so I can then sum hours for a monthly total.[/QUOTE]
=(b1-a1)*24 right click format click number |
Adding hours to dates
RajinCajun wrote on December 31, 1969 19:00 EST |
| I was wondering how would you add hours (for example time that it takes to complete a task) with a start date of lets say today 6/2/2006, and figure out the end date. Keep in mind the work week is 4 days long Monday through Thursday and then do the same thing this time using the full seven days of the week. Can this be even done with excel if so can you then edit the formula to exclude holiday dates? Please advice |
Requesting help on time sheet to round up to next hour
gycoso3 wrote on June 06, 2006 18:40 EST |
| I haven't had time to review all of the threads but was wondering if someone would be able to advise me what time of formula or how to edit the one I use to figure hours worked and then round it up to the next our for anything 1 minute past the hour or any amount of time worked. Here is the formula I am using... please help =(F5-D5+(F5<D5))*24 We use multiple columns since we are called out on a regular basis... I might be able to get it if someone get point me in the right direction. Thanks GYCOSO3 |
How to reverse the calculation|
Ben wrote on December 31, 1969 19:00 EST |
Hi,
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"...
Any help would be great, email me?
ben@bdesigned.co.uk
Thanks,
Ben |
Calculating time after midnight.
Tita wrote on December 31, 1969 19:00 EST |
I was trying to calculate the hours worked for a single employee on a specific day. Went crazy trying to get the thing to work. This simple formula worked for me.
Values...
Column A = time in, Column B= Time out (sometimes after midnight)
Column C = Total hours worked.
Make sure all your cells are formatted to the 24 hr clock (13:30)
FORMULA
=(24-A2+B2)
Use the 24 hrs. minus the time in + time out.
It seemed to work for me. |
davesexcel wrote on February 24, 2007 13:51 EST |
[QUOTE=gycoso3]I haven't had time to review all of the threads but was wondering if someone would be able to advise me what time of formula or how to edit the one I use to figure hours worked and then round it up to the next our for anything 1 minute past the hour or any amount of time worked. Here is the formula I am using... please help =(F5-D5+(F5<D5))*24 We use multiple columns since we are called out on a regular basis... I might be able to get it if someone get point me in the right direction. Thanks GYCOSO3[/QUOTE]
this formula gets the total hours worked and then rounds the total to the nearest 15 min
[CODE]=ROUNDDOWN(((E12<D12) E12-D12 (G12<F12) 2-F12)/TIME(0,15,0),0)*TIME(0,15,0)
[/CODE]
this rounding function will clock you in at the next 15min mark
punch in at 5:49
rounds up to 6:00
[CODE]=ROUNDUP(A1*96,0)/96[/CODE]
and of course this will clock you out at the last 15 mark
punch out at 3:40
rounds down to 3:30
[CODE]=ROUNDDOWN(A1*96,0)/96[/CODE] |
conversion of unit of 2sec to mins and hours
stevethank wrote on March 26, 2007 06:44 EST |
I have a unit that is 2secs, basically a recording. How do I convert this to mins and hours and check the time at say 200 unit point? The start point of the unit is 7:00AM.
Appreciate any suggestions. Please email to [email]stevethank@yahoo.com[/email]
Thanks
Steve |
Overtime Calculating
LizV wrote on May 03, 2007 22:21 EST |
I'm trying to create a time sheet that will calculate regular time, 1 1/2, and double time. Here is how it is set up:
Total Time Reg. 1 1/2 Double
20 8 4 8
Is there a way when you input the total time it will break it down?
Thanks for any help with this.
Liz |
Calculating Overtime and Double Time Hours based on time of day
philmcracken wrote on May 14, 2007 16:03 EST |
I have a situation where i need to track employee labor hours. These employees get paid overtime on any hours worked outside of 8am and 4:30 pm and after working 14 hrs they get double time. I would like to be able to just put in the employees start time and end time and have the spreadsheet calculate their number hours worked and how many of those hours were straight time, overtime and double time.
For example employee starts at 6am and ends at 10pm. I would like to input start time of 6am in column 1 and input end time of 8pm in column two. Then have the spreadsheet calculate straight time hours in column three (8am to 4:30pm = 8.5 straight time hrs), have spreadsheet calculate Overtime hours in column four (6am-8am and 4:30pm to 8pm = 5.5 overtime hours). In column five i want the spreadsheet to calculate double time hours (8pm-10pm = 2 double time hours). And finally sixth column total hours.
Columns 3, 4, & 5 present the challenges to me. Anybody have any solutions?
phil |
The 11 Immutable Laws of Internet Branding
