# how to calcualte and round times?

#### sloth101

##### New Member
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.

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### Datsmart

##### Well-known Member
If your time is in cell A1, this formula will give you the time rounded to the nearest quarter hour.
Code:
``=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

##### New Member
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

##### MrExcel MVP
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

##### Well-known Member
I assume from your first example your times are in columns B,C,D,and E.
Code:
``=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

##### New Member
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

##### Well-known Member
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

##### MrExcel MVP
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

##### New Member
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

Replies
21
Views
830
Replies
1
Views
913
Replies
8
Views
1K
Replies
3
Views
619
Replies
11
Views
236

1,191,274
Messages
5,985,702
Members
439,974
Latest member
sjoerdbosch

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