# Rounding Time from a decimal

#### Kopy

##### New Member
Hello,

I'm not very good with excel and I've been searching forums for help and there are some very close answers but nothing close enough. I'm trying to create a timesheet that takes into account multiple in and out times and uses decimals to denote time so 5am and 5pm both show as 5.00. The total hours should then be calculated out in quarter hours. I've provided an example below, in this instance the correct Total Hours for Friday would be 11.25, I just need a function that will help me get that. Any assistance would be great.

 In 5 Out 10 In 11 Out 5.21 In 0 Out 0 In 0 Out 0 Total Hrs 11.25

<colgroup><col><col></colgroup><tbody>
</tbody>

Last edited:

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
So if someone came in at 5 and left at 5.5, did they work for 0.5 hours or 12.5 hours?

Are the number of in/out pairs fixed and in certain rows?

Assuming rows 1-8 are in/out pairs:

B9 = <b7,b8+12,b8)-b7)+(if(b6<b5,b6+12,b6)-b5)+(if(b4<b3,b4+12,b4)-b3)+(if(b2<b1,b2+12,b2)-b1))*4,0) 4
<b7,b8+12,b8)-b7)+(if(b6<b5,b6+12,b6)-b5)+(if(b4<b3,b4+12,b4)-b3)+(if(b2<b1,b2+12,b2)-b1))*4,0) 4
round(((IF(B8 < B7,B8+12,B8)-B7)+(IF(B6 < B5,B6+12,B6)-B5)+(IF(B4 < B3,B4+12,B4)-B3)+(IF(B2 < B1,B2+12,B2)-B1))*4,0)/4
it keeps cutting off my formula<b7,b8+12,b8)-b7)+(if(b6<b5,b6+12,b6)-b5)+(if(b4<b3,b4+12,b4)-b3)+(if(b2<b1,b2+12,b2)-b1))*4,0) 4<b7,b8+12,b8)-b7)+(if(b6<b5,b6+12,b6)-b5)+(if(b4<b3,b4+12,b4)-b3)+(if(b2<b1,b2+12,b2)-b1))*4,0)="" 4<="" html=""></b7,b8+12,b8)-b7)+(if(b6<b5,b6+12,b6)-b5)+(if(b4<b3,b4+12,b4)-b3)+(if(b2<b1,b2+12,b2)-b1))*4,0)></b7,b8+12,b8)-b7)+(if(b6<b5,b6+12,b6)-b5)+(if(b4<b3,b4+12,b4)-b3)+(if(b2<b1,b2+12,b2)-b1))*4,0)></b7,b8+12,b8)-b7)+(if(b6<b5,b6+12,b6)-b5)+(if(b4<b3,b4+12,b4)-b3)+(if(b2<b1,b2+12,b2)-b1))*4,0)>

Last edited:
So if someone came in at 5 and left at 5.5, did they work for 0.5 hours or 12.5 hours?

Well if they typed 5.00 in and 5.50 out it would actually be .75 hours because the way our old spreadsheet worked it actually took the in/out times as actual time so 5.00 is 5:00am/pm. The old timesheet was using macros which is proving problematic for our Mac users, so I'm trying to make one that works for them. If they wanted to work over 5 hours 5:00a - 5:30pm they're required to take a 30 min break and the old timesheet knew that, that's a bit more complicated than I wanted to get into with this timesheet.

So in short for this 5.00 - 5.50 would be total hrs .75

I'm confused. Is the decimal portion minutes not portion of an hour?

I'm confused. Is the decimal portion minutes not portion of an hour?
I have that same question... for the 5.21 time, is that 21 minutes or 0.21 hours (that is, 21/100 of an hour)? And whichever it is, I presume the total is the same unit of measure.

I figured since the total rounded to .25 to get nearest quarter hour worked that the .21 was 21/100th of an hour.

I'm confused. Is the decimal portion minutes not portion of an hour?

Sorry I know it's a bit confusing. The decimal within the in/out does not represent quarter hours but actual time of day. The quarter time gets added at the end. So if someone writes 5.21 they actually mean 5:21 and then in the total hours it's converted to quarter hours. So 5.21 - 7.55 (5:21 - 7:55am/pm) is total hours 2.50

If it helps here's the website where the original document can be downloaded (toward the middle of the website) UCF F&A: IT Support: Forms

I figured since the total rounded to .25 to get nearest quarter hour worked that the .21 was 21/100th of an hour.

I took a quick look this is how the Minutes Convert:
Minutes - Quarter Hours
00-07 - .00
08-22 - .25
23-37 - .50
38-52 - .75
53-60 - 1.00

Replies
5
Views
399
Replies
3
Views
315
Replies
1
Views
273
Replies
4
Views
247
Replies
6
Views
304

### Forum statistics

1,203,321
Messages
6,054,717
Members
444,746
Latest member
Emre06 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back