Rounding Time from a decimal

[Kopy]

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.00
Out	10.00
In	11.00
Out	5.21
In	0.00
Out	0.00
In	0.00
Out	0.00
Total Hrs	11.25

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

 

[shg]

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

 

[par60056]

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)>

 

[Kopy]

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

 

[par60056]

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

 

[Rick Rothstein]

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.

 

[par60056]

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

 

[Kopy]

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

 

[Kopy]

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

 

[Kopy]

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