Convert decimal hours (.17 to 10 mins) — then round up to next quarter hour (10 mins = .25 hrs)

If you are rounding up to the nearest quarter hour the CEILING function works well:

Formula in I2, filled down:

=CEILING(G2,0.25)

Formula in H2, filled down:

=DURATION(,,G2)

N.B. those strange-looking leading ,, are needed in the formula.

Note that column H is not needed to derive I. You can just use CEILING directly.

Substitute ; for , in the formulas if your regions uses , as a decimal separator.

SG

You can try something like this:

enter the decimal hours in column B

for the duration format (hh mm)

select cell C2, then enter the formula:

=DURATION(0,0,B2)

if you really want this rounded to the nearest 10min, then use:

=DURATION(0,0,MROUND(B2, 10÷60))

shorthand for this is:

C2=DURATION(0,0,B2)

for the rounded decimal time:

D2=MROUND(B2,0.25)

to fill these down, select cells C2 and D2, copy

select cells C2 thru the end of column D, paste

Hi sasegars,

You wrote: I "need the time (listed as digital time) converted to hours + mins, then an additional column that shows rounded up time to next quarter hour"

Do these results match your needs?

Note the difference in the results in rows 4, 5, 6, 7 and 9 of columns I and J.

Column J uses Wayne's MROUND formula, which rounds the decimal value in column G up or down to the nearest quarter hour;

Column I uses ROUNDUP, multiplication and division to round the decimal value in column G up to the nearest quarter hour that is the same or later than the actual value.

Formulas:

H2: DURATION(0,0,G2)

Same as Wayne's, but with the cells formatted to show only hours and minutes.

I2: ROUNDUP(4×G2,0)÷4

This multiplies the number in column G by 4, rounds the result to the nearest whole number equal to or larger than the result, then divides that result by 4 to return the value in B rounded up to the nearer quarter hour at or greater thn the actual amount.

J2: MROUND(G2,0.25)

This is the MROUND formula from Wayne's example, which rounds the amount in column G to the nearer quarter hour, which could be less than or more than the actual amount.

Regards,

Barry

Two questions:

If you go the right formatting menu and select cells, can you format as Custom, Date/Time and then drag and drop the desired format items?

Have you looked at Round function or Roundup?

"=CEILING(G2,0.25)"

Good choice, SG!

CEILING is a function I've not used often, and one I seldom think of. Seems perfect for ths use, though.

Regards,

Barry