Time sheet set up specifically

  • Thread starter Thread starter HT
  • Start date Start date
H

HT

I need to create a time sheet whereas I can insert the amount of time worked
on a particular issue and have it add up..the catch is that it has to be
rounded off to the highest 1/4 hour.

For example,

Time worked: 45 minutes

Rounded off to : 1 hour

Thank you.
 
Hi!

Hmmm....

If you want to round up to the next increment of 15, how
would you handle 30 mins? 15 mins?

The general formula to roundup to the next increment of 15
would be:

=ROUNDUP(number/15,0)*15

Values like 45, 30, 15 would not round since they are
already multiples of 15.

So, if you round 45 mins up to an hour, what do you do
with 30 mins? Round it up to 45 mins?

Need more detail.

Biff
 
I'm not sure I follow. 0:45 rounded to the nearest 1/4 hour is 0:45, not
1:00. However, if you want 0:46 to round to 1:00, here's one way:

=CEILING(A1,TIME(0,15,0))

Format the cell as Time. Or, since XL stores times as fractional days
use the equivalent:

=CEILING(A1, 1/(24*4))

or, more efficiently

=CEILING(A1, 1/96)
 
P.S. -

Also, it depends on what type of time/number format you
are using.

The formula I posted is based on decimal values and not
time formats.

Biff
 
...sorry for the confusion..

15 minutes and anything multiple of 15 remains the same..so 15 minutes = 15
minutes, 30 minutes = 30 minutes..45 minutes = 45 minutes, 60 minutes = 60
minutes..but..the catch is lets say you have:

1 hour and 19 minutes..that should equal 1 hour and 1/2 hour..or 50 minutes
that should equal 1 hour..

35 minutes should round up to 45 minutes
52 minutes should round up to 1 hour

...its like that.

Thank you.
 
Then J.E.'s solution should work
--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Back
Top