I am working on a timesheet, and have a question. Due to a request by a user
to be able to only enter time with a signle keystroke, for example, 8:00,
only enter 8. Due to that, I have changed my cell formatting from time to
numbers.

Now, I need a formula that will round the number to a set of predetermined
minutes.

Example: If a user enters 8.25 for a time in, we would like a formula to
change the number to 8.30. I need the formula to do this for the following
entries:

0-14: 0
15-29: 15
30-44:30
45-59: 45

Any ideas, or am I very far off base?

Try this

=ROUNDDOWN(TIME(INT(A11),INT(MOD(A11,1)*60),0)*95, 0)/96

--

HTH

RP
(remove nothere from the email address if mailing direct)


"lbfries" wrote in message
...
I am working on a timesheet, and have a question. Due to a request by a
user
to be able to only enter time with a signle keystroke, for example, 8:00,
only enter 8. Due to that, I have changed my cell formatting from time to
numbers.

Now, I need a formula that will round the number to a set of predetermined
minutes.

Example: If a user enters 8.25 for a time in, we would like a formula to
change the number to 8.30. I need the formula to do this for the
following
entries:

0-14: 0
15-29: 15
30-44:30
45-59: 45

Any ideas, or am I very far off base?

Bob,

I'm not getting a correct rounding, so I believe that I have not given
enough information or the correct information:

My daily total of hours formula is:
=IF(D90,SUM(M9-L9)+(K9-J9)+(I9-H9)+(G9-F9)+(E9-D9),0) -- The IF statement
is there to have a user enter the IN time in the first IN field.

The formula to add up the week is: =SUM(N9:N15) Because the time entry is
just 8.00, or 9.00, etc...

So, when I round the weekly formula, I need the rounding to go to 15, 30, or
45.

Do I need to change my formulas?

IN
8.00

Rounded Total: 51.30 Weekly Totals: 51.27


"Bob Phillips" wrote:

Try this

=ROUNDDOWN(TIME(INT(A11),INT(MOD(A11,1)*60),0)*95, 0)/96

--

HTH

RP
(remove nothere from the email address if mailing direct)


"lbfries" wrote in message
...
I am working on a timesheet, and have a question. Due to a request by a
user
to be able to only enter time with a signle keystroke, for example, 8:00,
only enter 8. Due to that, I have changed my cell formatting from time to
numbers.

Now, I need a formula that will round the number to a set of predetermined
minutes.

Example: If a user enters 8.25 for a time in, we would like a formula to
change the number to 8.30. I need the formula to do this for the
following
entries:

0-14: 0
15-29: 15
30-44:30
45-59: 45

Any ideas, or am I very far off base?

Don't beat yourself up, complex details often take a few attempts to get
across.

Can I ask

- do you want to round, round up, or round down
- to the quarter hour or half hour?

Examples

what would you expect for

15:01
15:16
15::25
15:29

--

HTH

RP
(remove nothere from the email address if mailing direct)


"lbfries" wrote in message
...
Bob,

I'm not getting a correct rounding, so I believe that I have not given
enough information or the correct information:

My daily total of hours formula is:
=IF(D90,SUM(M9-L9)+(K9-J9)+(I9-H9)+(G9-F9)+(E9-D9),0) -- The IF
statement
is there to have a user enter the IN time in the first IN field.

The formula to add up the week is: =SUM(N9:N15) Because the time entry is
just 8.00, or 9.00, etc...

So, when I round the weekly formula, I need the rounding to go to 15, 30,
or
45.

Do I need to change my formulas?

IN
8.00

Rounded Total: 51.30 Weekly Totals: 51.27


"Bob Phillips" wrote:

Try this

=ROUNDDOWN(TIME(INT(A11),INT(MOD(A11,1)*60),0)*95, 0)/96

--

HTH

RP
(remove nothere from the email address if mailing direct)


"lbfries" wrote in message
...
I am working on a timesheet, and have a question. Due to a request by
a
user
to be able to only enter time with a signle keystroke, for example,
8:00,
only enter 8. Due to that, I have changed my cell formatting from
time to
numbers.

Now, I need a formula that will round the number to a set of
predetermined
minutes.

Example: If a user enters 8.25 for a time in, we would like a formula
to
change the number to 8.30. I need the formula to do this for the
following
entries:

0-14: 0
15-29: 15
30-44:30
45-59: 45

Any ideas, or am I very far off base?