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. How can I do this??
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
how do I round to nearest quarter hour
- Thread starter Firstime
- Start date
Yogi Anand
MrExcel MVP
- Joined
- Mar 12, 2002
- Messages
- 11,454
Hi FirstTime:
Welcome to MrExcel Board!
Please look at the following simulation ...
In my illustration, I have assumed you want to convert the time of 8:36 (8 hours and 36 minutes) to a decimal number representing hours and fractions of hours and have it rounded to the 1/4 hour mark. It is not clear from your post whether you want the number rounded up or down to the 1/4 hour mark -- so my illustration covers, rounding to the nearest, next(higher), or previous(lower) 1/4 hour mark.
I hope this helps -- otherwise explain a little further and then let us take it from there.
Welcome to MrExcel Board!
Please look at the following simulation ...
| y030923h1.xls | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | TimeInHours&Minutes | TimeInDecimalHours | ToNearestQuarterHour | ToNextQuarterHour | ToPreviousQuarterHour | ||
| 2 | 8:36 | 8.6 | 8.5 | 8.75 | 8.5 | ||
| 3 | 8.36 | 8.25 | 8.5 | 8.25 | |||
| 4 | 8.375 | 8.5 | 8.5 | 8.25 | |||
| 5 | 8.495 | 8.5 | 8.5 | 8.25 | |||
Sheet6 | |||||||
In my illustration, I have assumed you want to convert the time of 8:36 (8 hours and 36 minutes) to a decimal number representing hours and fractions of hours and have it rounded to the 1/4 hour mark. It is not clear from your post whether you want the number rounded up or down to the 1/4 hour mark -- so my illustration covers, rounding to the nearest, next(higher), or previous(lower) 1/4 hour mark.
I hope this helps -- otherwise explain a little further and then let us take it from there.
Upvote
0
Excel Facts
Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Pesky_Weasel
Board Regular
- Joined
- Sep 28, 2002
- Messages
- 99
Yogi,
Good solution.
That was embarrasingly simple.
Regards
Weasel
Upvote
0
Yogi Anand
MrExcel MVP
- Joined
- Mar 12, 2002
- Messages
- 11,454
Hi Weasel:
It is all in team work -- let us keep EXCELing!
It is all in team work -- let us keep EXCELing!
Upvote
0
Did you ever figure it out so far? I need the same thing!!! You would think someone would post one online for everyone - so far no luck
This is as far as I got on mine, but I still have not figured out how to take the total and make it in quarter increments for payroll.
I took IN time (A1) and OUT time(A2) (format as TIME PM) then totaled them in (C1 as format time 13:30)
(breaking up morning and afternoon to punch out for lunch I added (D1) IN time again (E1) OUT time then totaled them in F1 formatted as time 13:30 then in G1 is the total in hours and min. In H1 it is in number format which converts it to a decimal... only problem is that I need it to become quarter time for payroll.
So it looks like this:
IN OUT TOTAL IN OUT TOTAL = TOTAL in hours then total converted to Decimal
I have an attachement I can send to someone if they can help me- I am not good at formulas so this is all greek to me.
This is as far as I got on mine, but I still have not figured out how to take the total and make it in quarter increments for payroll.
I took IN time (A1) and OUT time(A2) (format as TIME PM) then totaled them in (C1 as format time 13:30)
(breaking up morning and afternoon to punch out for lunch I added (D1) IN time again (E1) OUT time then totaled them in F1 formatted as time 13:30 then in G1 is the total in hours and min. In H1 it is in number format which converts it to a decimal... only problem is that I need it to become quarter time for payroll.
So it looks like this:
IN OUT TOTAL IN OUT TOTAL = TOTAL in hours then total converted to Decimal
I have an attachement I can send to someone if they can help me- I am not good at formulas so this is all greek to me.
Upvote
0
Similar threads
- Solved
