# Excel Issues - rounding time - Office 97



## pinseaking (Jan 6, 2006)

I'm dating myself here, but my office is the 97 version.

I assist in running a very small 18 room boarding house. We have software that logs all the calls made from each room. The software failed when we put a new telco system in and the vendor is working on the fix, however until then we have to print out raw data from hyperterminal.

This is the problem:

We need to sort this information for each room and round the times for billing.
We can get the raw data in excel and sort it for each room however, we are having issues with rounding the time.
I've been working on this for 3 days now and read everything that I can on the subject without being committed to an institution.
Assistance in this matter would be greatly appreciated.

Dave "Rocky" L:4-dontkno 
Chevy Chase Club
rocky(at)chevychaseclub.org


----------



## ReeKorl (Mar 25, 2005)

How exactly do you need the time rounded? nearest minute, 5 minutes, quarter hour, hour?

If you have the time in excel as excel normally understands time (ie a full day is actually input as 1, 12 hours is 0.5, etc) and just formatted as 'time' it should be reasonably simple.

If you need the time rounded to nearest 5 minutes, divide the time by 5/1440 (5 minutes rounding, 1440 minutes in the day), round to nearest whole number, then multiply by 5/1440 again. If you want it to the hour, divide by 60/1440 (or 1/24 if you want) round, multiply by the same value.

A sample formula would be if you have the time in cell A1 and you want it rounded to the nearest 5 minutes:

=ROUND(A1/(5/1440),0)*(5/1440)

If you need clarification on this, attach a small sample sheet of data and I'll sort it so you can see it working. You'll need to rename the .xls file to something like .txt or zip it to upload it here.


----------



## sweetloop (Sep 29, 2004)

It depends on what format your data is in. Here's a link that explains a bit about working with time formats in Excel. It's got some good examples too.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q214094

If you need additional help it might be useful if you could post an example of some of the data you're working with.


----------

