rounding time question

[Guest]

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?

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.

 

[Fredrik Wahlgren]

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?

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.

I assume your sheet is made in such a way that people are entering the time
manually? I waould like to know a few more details, like the exact string
that you enter. I guess you could put the code in a worksheet cahnge event.

/Fredrik

 

[Guest]

The time is entered in the worksheet as follows:

IN: 8:00 OUT: 12:00 IN: 13:00 OUT:17:00

The formula to sum the amount of hours worked is as follows:
=SUM(M9-L9)+(K9-J9)+(I9-H9)+(G9-F9)+(E9-D9) With M9 being an OUT time, and
L9 being an IN time.

 

[Ron Rosenfeld]

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