Rounding off Time in Excel 2

[BC000]

I have two entries, both times and both cells formatted as hh:mm. Cell A1 has 02:00 in it and cell B1 has 02:55 in it. I now want to find the difference in cell C1. Simple =B1-A1. But I also want to round off the result to the nearest hour. Instead of getting 00:55 in cell C1, I want 01:00.

How do I achieve this. I tried using the Round function, but was unsuccessful. Can someone show me the correct formula in cell C1 and also what should be the formatting of cell C1.

Thanks.

 

[combo]

Use MROUND function (Analysis ToolPak addin), syntax:
=MROUND(A1,1/24)

combo

 

[BC000]

Thank You Combo,

It worked. Will the add-in stay with the Excel file, or do I have to activate it everytime? Say if I open the file on another computer, will this add-in install automatically?

Sorry, but I am not an advanced Excel user.

Thanks.

 

[GlennUK]

An alternative without having to use Analysis Toolpak is:

=ROUNDUP(A1*24,0)/24

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[GlennUK]

Or even

=CEILING(A1,1/24)

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[combo]

..or still with the ROUND function (nearest hour, with 30' rounded up): =ROUND(A1*24,0)/24

combo

 

[GlennUK]

So many ways for that poor cat. ;-)

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[BC000]

Thank you all for your input. Much appreciated.

 

[SkipVought]

BC000,

I notice that over the past 2 years, you have posted 7 threads and have received many good tips related to your stated needs. Yet, you have responded not ONCE, to
[blue]
Thank Tek-Tip Contributor
for this valuable post![/blue].

The Stars accomplish several important things.

First, it gives positive feedback to contributors, that their posts have been helpful.

Second, it identifies threads as containing helpful posts, so that other members can benefit.

And third, it identifies the original poster (that's YOU, BTW), as a grateful member, that not only receives, but is willing to give tokens of thanks.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]