 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2008TimeSheet Using 100 Minutes & Rounding to .25
I am trying to create a spreadsheet to calculate employee hours. We use 100 minutes instead of 60. (Ex 15:50 = 3:30) We round based on 5's to the hour (Ex 9:92 = 10 9:90 =9.75) Overtime is calculated by weekly Hours, Not Daily Here is an example of punch times: Day Time In Time Out Total Hrs Monday 7:45 15:55 8.10 (8 Hrs 6 Min Round to 8) Tues 7:44 15:54 8.10 (8 Hrs 6 Min Round to 8) Wed 7:45 15:86 8.41 (8 Hrs 25 Min Round to 8.5) Thurs 7:48 15:54 8.06 (8 Hrs 4 Min Round to 8) Fri 7:46 16:52 9.06 (9 Hrs 4 Min Round to 9) Sat 7:94 13:17 5.23 (5 Hrs 14 Min Round to 5.25) This is what I need it to look like. The cell under the day should be a formula if possible. The cell under the Reg Hrs should be a formula as the hours will not always be 40. The cell under the OT should be a formula for anything over the 40. Employee Mon Tues Wed Thurs Fri Sat Sun Total Hrs Reg Hrs OT Hrs Bill 8 8 8.5 8 9 5.25 0 46.75 40 6.75 Why do you use a dyslexic way of representing time. Hold it as time and it all becomes simple.  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >I am trying to create a spreadsheet to calculate employee hours. > We use 100 minutes instead of 60. (Ex 15:50 = 3:30) [quoted text clipped - 21 lines] > Bill 8 8 8.5 8 9 5.25 0 46.75 40 > 6.75 You can simplify your life greatly by using one of two options: 1. If the most important thing is having a colon in your numbers, then use real times (ie, 60 minute hours). Excel can then handle all needed calculations. To round to 5 minutes, use =mround(a1,time(0,5,0)) 2. If, however, dividing hours into 100 parts is more important, then use a decimal. Now you are just entering the number of hours to two decimal places. To round to 5/100 of an hour, use =mround(a1,.05) Regards, Fred. >I am trying to create a spreadsheet to calculate employee hours. > We use 100 minutes instead of 60. (Ex 15:50 = 3:30) [quoted text clipped - 21 lines] > Bill 8 8 8.5 8 9 5.25 0 46.75 40 > 6.75
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.