Can you round numbers to display a specific set of numbers, for e.

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" <[email protected]> wrote in message
news:[email protected]...
> 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(D9>0,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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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(D9>0,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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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?
> >
> >
> >