On Wed, 6 Apr 2005 09:11:13 -0700, "lbfries"
wrote:

I am working on a timehsheet in which a user has requested the times be
rounded in the following fashion:

1-14 minutes= round down to zero
15-22 minutes= round down to 15
23-27 minutes=round up to 30 minutes
38-52 minutes=round down to 45
53-60 minutes=round to next hour

Is this possible in Excel?

Set up a two column table as follows:

0 00:00
15 15:00
23 30:00
38 45:00
53 00:00

To obtain the values in the second column, you may enter them as
0
0:15
0:30
0:45
1:00

Then use this formula:

=FLOOR(A1,1/24)+VLOOKUP(MINUTE(A1),tbl,2)

where "tbl" is the reference to the above table.


Also, can you change the Excel time format to display time as "regular time"
and not military time for time entry? A user does not want to have to enter
1:00 pm as 13:00 when entering his/her time. I can't find a function to do
that.

To DISPLAY time, look at Format/Cells/Number/Time or set up a custom format.

To ENTER times, the user may also use "1 P" without the quotation marks for
1PM. This may depend on your regional settings, though


--ron