how to calcualte and round times?

[sloth101]

hi all, at my current work place they use an excel time sheet formatted like so:
_____|__IN__|__OUT__|__IN__|__OUT__|__TOTAL__|
Date:|_7:05____12:13____12:46___5:28______10_____
Date|___________________________________10_____
Date|___________________________________10_____
~
~
Weekly total______________________________30______

So we work 7:00am to 5:30pm with a half hour lunch 3 days a week. HR wants us to write the time exactly when we punch in and out but to round to the nearest quarter hours for our daily total in decimal format. For instance anything below :06 rounds down and anything above :07 round up so 7:06 is 7.00 and 7:07 is 7.25. what I want to do is have the TOTAL column auto calculate the time worked for the day and automatically round to the nearest quarter hour in decimal form so say I worked 10hrs:7min it would round that to 10:15 and convert it 10.25

Any help is very appreciated as I am almost clueless to excel commands and such. Thanks much.

 

[Datsmart]

If your time is in cell A1, this formula will give you the time rounded to the nearest quarter hour.

=ROUND(A1*96,0)/96*24

Format cell as number with two decimal places.

By the way, 7:07 would not round to the quarter hour unless it is greater than 7:07:29.

 

[sloth101]

thanks for the reply, that only seems to give me a #VALUE. also like I said the IN and OUT columns need to be exact time like I showed but the TOTAL column has to be rounded to quarter hour increments so the basic function for the TOTAL column would be =SUM(A2-A1)+(A4-A3) but in that formula I need it to round for all hours of the day, my co-worker got it to kind of work but his code is outrageously long and he has to do it for every quarter of every hour on every line, I'm just wondering if there is a way to simplify the process.

 

[barry houdini]

A little like John suggests....all in one formula

=ROUND((A4-A3+A2-A1)*96,0)/4

format as number

for your example you should get 9.75

 

[Datsmart]

I assume from your first example your times are in columns B,C,D,and E.

=ROUND((SUM(C2-B2)+(E2-D2))*96,0)/96*24

This formula references those IN and OUT times. If your data is vertical as the formula in your last post suggests you will have to adjust those cell references.

 

[sloth101]

Those both worked great! thanks a lot guys, one last question, is it possible to display the cells with the time in them in a time format, when I used the formulas it converted the time into a decimal? it doesn't have to have AM or PM, but I would like them to show like in the example with a colon, 12hr or 24hr is acceptable.

 

[Datsmart]

Initially you asked for Decimal Time. If you now want regular Time, just remove the "*24" from the end of my formula and format the cell as Time. You can choose to use 12/24 or AM/PM, your choice.

 

[barry houdini]

Are you talking about the result? I thought you wanted to show 10.25 rather than 10:15? If you want the result in time format try just

=ROUND((A4-A3+A2-A1)*96,0)/96

It's also possble to use MROUND function if you have Analysis ToolPak add-in installed, that formula is perhaps more "transparent"

=MROUND(A4-A3+A2-A1,"0:15")

 

[sloth101]

I just needed the TOTAL column to appear as a decimal and that is working great, and taking of the 24 worked perfectly, thanks again for your help, it is now doing what I wanted it to