Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Calculating Overtime from Hours total
I am working with a bi-weekly timesheet that calculates total hours in
decimal format. I need to calculate the overtime after 8 hours in a day. C10 shows a clock in time of 8:39 D10 shows a clock out time of 13:00 E10 shows a clock in time of 13:30 F10 shows a clock out time of 17:14 formula in G10 is as follows --- =(D10-C10+F10-E10)*24 giving a total of 8.08 hrs worked H10 needs to show ONLY the .08 of the 8.08 AND is there a way to make the hours in G10 not show hrs in excess of 8.00 at the same time? Your help is appreciated!! Dreamweavn |
#2
|
|||
|
|||
If you just want to *display* 8.00 in G10, but leave the value alone,
select G10, then choose Format/Cell/Number/Custom and enter [<=8]0.00;"8.00" then use H10: =MAX(G10-8,0) If you want instead to have the actual value in G10 be 8 or less: G10: =MAX(8,(D10-C10+F10-E10)*24) H10: =MAX(0,(D10-C10+F10-E10)*24-8) In article , "Dreamweavn via OfficeKB.com" wrote: I am working with a bi-weekly timesheet that calculates total hours in decimal format. I need to calculate the overtime after 8 hours in a day. C10 shows a clock in time of 8:39 D10 shows a clock out time of 13:00 E10 shows a clock in time of 13:30 F10 shows a clock out time of 17:14 formula in G10 is as follows --- =(D10-C10+F10-E10)*24 giving a total of 8.08 hrs worked H10 needs to show ONLY the .08 of the 8.08 AND is there a way to make the hours in G10 not show hrs in excess of 8.00 at the same time? Your help is appreciated!! Dreamweavn |
#3
|
|||
|
|||
Probably a typo:
G10: =MAX(8,(D10-C10+F10-E10)*24) Should be: G10: =MIN(8,(D10-C10+F10-E10)*24) Biff "JE McGimpsey" wrote in message ... If you just want to *display* 8.00 in G10, but leave the value alone, select G10, then choose Format/Cell/Number/Custom and enter [<=8]0.00;"8.00" then use H10: =MAX(G10-8,0) If you want instead to have the actual value in G10 be 8 or less: G10: =MAX(8,(D10-C10+F10-E10)*24) H10: =MAX(0,(D10-C10+F10-E10)*24-8) In article , "Dreamweavn via OfficeKB.com" wrote: I am working with a bi-weekly timesheet that calculates total hours in decimal format. I need to calculate the overtime after 8 hours in a day. C10 shows a clock in time of 8:39 D10 shows a clock out time of 13:00 E10 shows a clock in time of 13:30 F10 shows a clock out time of 17:14 formula in G10 is as follows --- =(D10-C10+F10-E10)*24 giving a total of 8.08 hrs worked H10 needs to show ONLY the .08 of the 8.08 AND is there a way to make the hours in G10 not show hrs in excess of 8.00 at the same time? Your help is appreciated!! Dreamweavn |
#4
|
|||
|
|||
Thanks for the quick response! The following does not allow just 8.00 to
appear in G10. It still shows 8.08. =MAX(8,(D10-C10+F10-E10)*24) I should have thought a step ahead ...calculating the day itself is fine but to actaully figure overtime for hours over 40 this does not work, my fault on that. Can you tell me how to get the following... G13 shows the total for week 1 (in this case 24.0) H13 shows the OT total for week 1 (in this case .43) Of course you can now see why I should have thought a step ahead. OT is not valid if the hrs worked was only 24, however, lets say the hours were 40.43. How do I get G13 to show 40 total and H13 to show the remaining .43 OT total but NOT show the .43 if G13 is not over 40? Dreamweavn |
#5
|
|||
|
|||
See Chip Pearson's site.
http://www.cpearson.com/excel/overtime.htm "Dreamweavn via OfficeKB.com" wrote in message ... Thanks for the quick response! The following does not allow just 8.00 to appear in G10. It still shows 8.08. =MAX(8,(D10-C10+F10-E10)*24) I should have thought a step ahead ...calculating the day itself is fine but to actaully figure overtime for hours over 40 this does not work, my fault on that. Can you tell me how to get the following... G13 shows the total for week 1 (in this case 24.0) H13 shows the OT total for week 1 (in this case .43) Of course you can now see why I should have thought a step ahead. OT is not valid if the hrs worked was only 24, however, lets say the hours were 40.43. How do I get G13 to show 40 total and H13 to show the remaining .43 OT total but NOT show the .43 if G13 is not over 40? Dreamweavn |
#6
|
|||
|
|||
Error in copying, Should be MIN
In article , "Dreamweavn via OfficeKB.com" wrote: =MAX(8,(D10-C10+F10-E10)*24) |
#7
|
|||
|
|||
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating timesheet, time-in/time-out = total hours & minutes, . | Excel Worksheet Functions | |||
need help w/formula for calculating overtime hours | Excel Worksheet Functions | |||
Problems calculating total hours | Excel Discussion (Misc queries) | |||
Subtracting paid hours from unpaid hours | Excel Worksheet Functions | |||
Help! I am stuck calculating Days, Hours, Mins please help | Excel Worksheet Functions |