rounding in quarter increments

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.

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.1sm22a_1122102321.2141@excelforum-nospam.com>,
toastnbutter
<toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com> 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" <toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com>
wrote in message
news:toastnbutter.1sm22a_1122102321.2141@excelforum-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
>

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.1sm22a_1122102321.2141@excelforum-nospam.com>,
toastnbutter
<toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com> 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" <toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com>
wrote in message
news:toastnbutter.1sm22a_1122102321.2141@excelforum-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
>

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.1sm22a_1122102321.2141@excelforum-nospam.com>,
toastnbutter
<toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com> 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" <toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com>
wrote in message
news:toastnbutter.1sm22a_1122102321.2141@excelforum-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
>

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.1sm22a_1122102321.2141@excelforum-nospam.com>,
toastnbutter
<toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com> 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" <toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com>
wrote in message
news:toastnbutter.1sm22a_1122102321.2141@excelforum-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
>

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" <toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com>
wrote in message
news:toastnbutter.1sm22a_1122102321.2141@excelforum-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
>

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.1sm22a_1122102321.2141@excelforum-nospam.com>,
toastnbutter
<toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com> 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" <toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com>
wrote in message
news:toastnbutter.1sm22a_1122102321.2141@excelforum-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
>

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.1sm22a_1122102321.2141@excelforum-nospam.com>,
toastnbutter
<toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com> 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" <toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com>
wrote in message
news:toastnbutter.1sm22a_1122102321.2141@excelforum-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
>

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.1sm22a_1122102321.2141@excelforum-nospam.com>,
toastnbutter
<toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com> 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" <toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com>
wrote in message
news:toastnbutter.1sm22a_1122102321.2141@excelforum-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
>

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.1sm22a_1122102321.2141@excelforum-nospam.com>,
toastnbutter
<toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com> 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" <toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com>
wrote in message
news:toastnbutter.1sm22a_1122102321.2141@excelforum-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
>

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.1sm22a_1122102321.2141@excelforum-nospam.com>,
toastnbutter
<toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com> 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" <toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com>
wrote in message
news:toastnbutter.1sm22a_1122102321.2141@excelforum-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
>

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.1sm22a_1122102321.2141@excelforum-nospam.com>,
toastnbutter
<toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com> 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.

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.1sm22a_1122102321.2141@excelforum-nospam.com>,
toastnbutter
<toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com> 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" <toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com>
wrote in message
news:toastnbutter.1sm22a_1122102321.2141@excelforum-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
>

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" <toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com>
wrote in message
news:toastnbutter.1sm22a_1122102321.2141@excelforum-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
>

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.1sm22a_1122102321.2141@excelforum-nospam.com>,
toastnbutter
<toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com> 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.

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.1sm22a_1122102321.2141@excelforum-nospam.com>,
toastnbutter
<toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com> 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" <toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com>
wrote in message
news:toastnbutter.1sm22a_1122102321.2141@excelforum-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
>

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.1sm22a_1122102321.2141@excelforum-nospam.com>,
toastnbutter
<toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com> 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" <toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com>
wrote in message
news:toastnbutter.1sm22a_1122102321.2141@excelforum-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
>

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.1sm22a_1122102321.2141@excelforum-nospam.com>,
toastnbutter
<toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com> 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" <toastnbutter.1sm22a_1122102321.2141@excelforum-nospam.com>
wrote in message
news:toastnbutter.1sm22a_1122102321.2141@excelforum-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
>