TimeSheet Using 100 Minutes & Rounding to .25

[Formula Confusion]

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

 

[Bob Phillips]

Why do you use a dyslexic way of representing time. Hold it as time and it
all becomes simple.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Fred Smith]

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.