I am trying to find the best formula to demonstrate the length of time
it takes to perform tasks and then round them to quarter day
increments. For example, a task takes 10 hours to perform I need a
formula that will take the 10 hours and display 1.25 (assuming an 8
hour work day).

By the same token though if it is 10.5 hours it should roundup to 1.5
and not display 1.3.

Any help would be greatly appreciated.


--
toastnbutter
------------------------------------------------------------------------
toastnbutter's Profile: http://www.excelforum.com/member.php...o&userid=25511
View this thread: http://www.excelforum.com/showthread...hreadid=389556

One way:

If your times are in XL time format (e.g., 10:00), then


=CEILING(D30*3,0.25)

(The 3 comes from 8 hours being 1/3 of a day, so hours need to be
multiplied by 3).

If instead your times are entered as number of hours (e.g., 10.0, 10.5),
then

=CEILING(A1/8,0.25)




In article ,
toastnbutter
wrote:

I am trying to find the best formula to demonstrate the length of time
it takes to perform tasks and then round them to quarter day
increments. For example, a task takes 10 hours to perform I need a
formula that will take the 10 hours and display 1.25 (assuming an 8
hour work day).

By the same token though if it is 10.5 hours it should roundup to 1.5
and not display 1.3.

Any help would be greatly appreciated.

Hi,

if total hours number is in [A1], then try this:

1) for normal rounding

=ROUND(A1/8/0.25,0)*0.25

2) for rounding up (which is what your example suggests)

=ROUNDUP(A1/8/0.25,0)*0.25
=CEILING(A1/8/0.25,1)*0.25

3) for rounding down

=ROUNDDOWN(A1/8/0.25,0)*0.25
=INT(A1/8/0.25)*0.25
=TRUNC(A1/8/0.25)*0.25
=FLOOR(A1/8/0.25,1)*0.25

you can also use the MROUND function, which is part of the ATP (Analysis
ToolPack), but I never recommend those formulas for compatipility reasons.

Regards,
KL


"toastnbutter"
wrote in message
news:toastnbutter.1sm22a_1122102321.2141@excelforu m-nospam.com...

I am trying to find the best formula to demonstrate the length of time
it takes to perform tasks and then round them to quarter day
increments. For example, a task takes 10 hours to perform I need a
formula that will take the 10 hours and display 1.25 (assuming an 8
hour work day).

By the same token though if it is 10.5 hours it should roundup to 1.5
and not display 1.3.

Any help would be greatly appreciated.


--
toastnbutter
------------------------------------------------------------------------
toastnbutter's Profile:
http://www.excelforum.com/member.php...o&userid=25511
View this thread: http://www.excelforum.com/showthread...hreadid=389556