roundup time

momshoe

New Member
Joined
May 17, 2006
Messages
6
I need a formula to round up to the next hour (if over 10 min.) Example

7:30 to 8:55 = 1.25 hrs needs to be 2 hrs.

If less than 1 hr need to return 1 hr.

caculating time with round up for anything over 10 min. into the next hour.

Hope this helps
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,439
Office Version
  1. 365
Platform
  1. Windows
momshoe

Welcome to the Mr Excel board!

Does this do what you want?
=INT(1+(B1-A1-1/144)/(1/24))
 

momshoe

New Member
Joined
May 17, 2006
Messages
6

ADVERTISEMENT

momshoe

Welcome to the Mr Excel board!

Does this do what you want?
=INT(1+(B1-A1-1/144)/(1/24))

This returns #name

now what????
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,439
Office Version
  1. 365
Platform
  1. Windows
momshoe

Are you using cells A1 and B1 as in the suggested formulas?
Please tell us exactly what is in the two cells. If a formula, please post the formula and the result on the board. If a value, please tell us exactly what value was typed/pasted etc in the cells.
Please tell us how the two cells are formatted.

Both formulas (onlyadrafter's and mine) produce numerical values for me (with slightly different results for some time values).

Here is a sheet with times in columns A and B.
My formula is used in column D
onlyadrafter's formula is used in column F
There are no problems with #VALUE or #NAME so there must be something different with your sheet and we need to find out what.
Mr Excel.xls
ABCDEFG
17:308:5122
27:308:5222
37:308:5022
47:307:5111
57:308:4922
67:307:3100
77:308:4021
87:3010:3933
97:3010:4043
107:308:5122
117:308:5122
12
Times
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,439
Office Version
  1. 365
Platform
  1. Windows
what format do I use for each column?
I have columns A & B formatted as Custom [h]:mm and the result column as General.
 

Forum statistics

Threads
1,136,655
Messages
5,677,019
Members
419,668
Latest member
DharmaK

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top