Can you round up time? - Excel

[Capt BK]

Hi everyone,

I use an excel spreadsheet as a back up for my logbook (as do many it seems from the search) and was wondering if you could round up time. Eg:

using [hh]:mm = 39:55

using [hh] = 39

Is there anyway you can make it show 40?

Before anyone says dont worry about 5 minutes in your logbook, I'm not. I'm just starting to learn excel and all the different formulas possible amaze me and I wonder if there is one for this as well? I've tried using =ROUND but quite frankly I confuse easily

Of course one option is to use decimal time!

CBK

[Jhieminga]

Interesting question!

As far as I know (but I don't know everything!) there is no easy way to do this. The reason behind this is that Excel has a strange way of working with time and dates behind the scenes. Basically it boils down to the fact that a date/time is always seen by Excel as a decimal number , with 1 being 1st January 1900 or thereabouts. The fact that the cell shows something different is just the formatting, it doesn't actually store the information as a true date/time. Because of this you can easily add/subtract dates from eachother as for Excel this looks like 17538 - 17534. Time is now found in the part of the number behind the comma, so 1,5 is 12:00 on 1st January 1900.

This is were the problem with rounding arises: when you have 0:45 in a cell, Excel sees this as the decimal number 0,03. When rounding this it will always go to zero. 39:55 is seen by Excel as 1,66 (1 day plus 0,66 of a day), which rounds to 2, and is then shown as 48:00 as this means two days when formatted as [hh]:mm.

I've been trying to think of a way to work around this (might find the solution useful myself) and am working on a solution using the IF function. I just need to think of a way to compute the correct rounded figure! I'll see if I can get it sorted and post it here.

[Llademos]

One way of doing it, albeit a bit long winded:

Assuming the original time is in cell A1

Cell B1: =if(minute(A1)>0,Hour(A1)+1,Hour(A1))

A1 gives the time as entered HH:MM, B1 shows the rounded up time as a number

[Jhieminga]

Aahhh, thanks for that Llademos as that gave me the tip needed to crack it!

You don't have to hide any colums, just paste this rather lengthy formula in a cell: =IF((MINUTE(I269)>30);(DAY(I269)+TIME(HOUR(I269)+1;0;0));(DA Y(I269)+TIME(HOUR(I269);0;0)))

I've used I269 as a reference cell here, replace this (five times) with the one you need.
The first statement looks if the minutes part of the time are more than 30. Insert other number if needed (0 for example if you want to always round up).
The second section gives the rounded up time, the third section gives a rounded down time, always to the nearest hour.
Format the cells as [hh]:mm to keep it looking right



Edited to correct for hours over 24!

[Capt BK]

WOW

Thanks for the help. There are far greater minds at work here then my miniscule nugget! Unforunately I still cant get it to work.

Putting 39:55 in Cell A1 and using the Llademos method gives me an answer of 384:00

same again using the Jhieminga method (remembering to adjust all references to cell A1) gives an error message. Returning to the formula the cursor is placed "=IF((MINUTE(I269)> 30) here ;(DAY(I269)+TIME(HOUR(I269)+1;0;0));(DAY(I269)+TIME(HOUR(I26 9);0;0)))

All cells formatted at [hh]:mm

Sorry for putting this on you, I know you like a challenge

CBK

[Jhieminga]

Capt BK, when transferring the formula to the cell, make sure that no extra spaces creep into it. Because of the formatting of this forum the formula breaks across several lines, and in copying this may lead to errors. In your post a space has appeared in front of the number 30, this may have caused the error (although I cannot reproduce the error by placing a space there, odd! ) also make sure there is no double apostrophe in front of the formula when it is in the cell.

Which version of Excel are you using, is it an English language version??

The reason Llademos method gives that answer is that it converts to a number, you should format the cell with the result as such and then you'll see '40'. It is now showing you 40 days displayed in hours.

[Capt BK]

Thanks Jhieminga

I've double checked it and removed the space but still cant get it to work? ?

Using Excel 2002 and it is an english version.

If I PM my email to you will you please send me an example spreadsheet and i'll copy and paste directly.

Thanks

Mike

[Jhieminga]

E-mail on the way!

[Tinstaafl]

I haven't tried this, but it might be possible to use the '=ROUND' function if you specify an appropriate number of decimal places as one of the function's arguments.

[Roger That]

A quick search revealed this from Exceltip.com .

Seems like it's trying to do the same thing as your query (?)

RT

[R4+Z]

you can use the round worksheet function. the two formulae below are examples, the first rounds to the half hour and the second to the quarter.

=ROUND(A1*24/0.5 ,0)*0.5 /24
=ROUND(A1*24/0.25,0)*0.25/24

The bits in red are what you change to determine the roundup value (1/4 hour 0.25 and 1/2 hour 0.5).

If you simply want time to the nearest hour then the formula is shorter.

=ROUND(A1*24,0)/24

Don't forget the [hh]:mm formatting


hope this helps

R4

[ExGrunt]

Excel International Versions

Capt BK

Your problem with Jhieminga's formula may be one of internationalisation.

He is working with a version of Excel which uses commas as the decimal separator in the continental fashion eg: a half is 0,5 not 0.5.

The consequence is that the formula separator in Excel becomes a semi-colon. To resolve this problem change the semi-colons to commas:

"=IF((MINUTE(I269)> 30),(DAY(I269)+TIME(HOUR(I269)+1),0),0))),(DAY(I269)+TIME(HOUR(I269)),0),0)))"

Hope this helps

EG

[Jhieminga]

ExGrunt, thanks for that! It seems that you found the exact problem we were suffering from! I hadn't realised that that specific difference existed between the Excel versions!

I sent Capt BK an excel sheet via e-mail last night and that worked, but it must have converted the semi-colons to commas automatically.

Some of the other tips above look promising as well, at least they look simpler than my solution!

[Capt BK]

Thanks everyone,

Jhieminga sent me an email with the formulas in and I got it working, looks like ExGrunt hit the nail on the head with the versions etc

I'll try the other suggestions as well, I get the impression that i'm getting ahead of myself but it's suprisingly interesting. Damn my curiosity

CBK

[Gunner B12]

Capt BK

I also used to just do the odd thing in Excel until someone showed me how to record macros. Then when I realised it used a programming language similar to that I played with all those years ago on my trusty sinclair I really took off. I now have an excel spreadsheet with massive macros sitting behind it which does 8hrs work in about 3 mins.

[Tinstaafl]

Pretty sure you can specify in Excel's options what nationalisation features you use eg decimal/comma separators etc