Does anyone know how to project the due date according to a required number
of working hours? such as

If today is 24 Oct, 2005 (Monday) at 9:00am, my project required 100 hours
to be finished, then how to create a formula and to project the due date in
excel sheet?

Working hour: 9am - 1pm and 2pm - 5pm (total 8 working hour per day)

Thank for any suggestion
Eric

Eric,

Try this

=workday(A30,INT(A29/8))+MOD(A30,1)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/
24)13/24)/24)+(N((MOD(A30,1)+MOD(A29,8)/24)17/24)/24)

This assumes weekdays only. IF you want to include Sat anmd Sun, use

=A30+INT(A29/8)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/24)13/24)/24)+(N((M
OD(A30,1)+MOD(A29,8)/24)17/24)/24)

Both assume the start date/time in A30, the number of hours in A29

--

HTH

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


"Eric" wrote in message
...
Does anyone know how to project the due date according to a required
number
of working hours? such as

If today is 24 Oct, 2005 (Monday) at 9:00am, my project required 100 hours
to be finished, then how to create a formula and to project the due date
in
excel sheet?

Working hour: 9am - 1pm and 2pm - 5pm (total 8 working hour per day)

Thank for any suggestion
Eric

Thank Bob Phillips
As I key in following code
=workday(A30,INT(A29/8))+MOD(A30,1)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/24)13/24)/24)+(N((MOD(A30,1)+MOD(A29,8)/24)17/24)/24),
then it displays #NAME?
There is something wrong with workday function, do you have any idea on how
to fix it?

Thank you very much
Eric


"Bob Phillips" wrote:

Eric,

Try this

=workday(A30,INT(A29/8))+MOD(A30,1)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/
24)13/24)/24)+(N((MOD(A30,1)+MOD(A29,8)/24)17/24)/24)

This assumes weekdays only. IF you want to include Sat anmd Sun, use

=A30+INT(A29/8)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/24)13/24)/24)+(N((M
OD(A30,1)+MOD(A29,8)/24)17/24)/24)

Both assume the start date/time in A30, the number of hours in A29

--

HTH

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


"Eric" wrote in message
...
Does anyone know how to project the due date according to a required
number
of working hours? such as

If today is 24 Oct, 2005 (Monday) at 9:00am, my project required 100 hours
to be finished, then how to create a formula and to project the due date
in
excel sheet?

Working hour: 9am - 1pm and 2pm - 5pm (total 8 working hour per day)

Thank for any suggestion
Eric

WORKDAY is part of the analysis toolpak add-in. Make sure you have that
installed.

--

HTH

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


"Eric" wrote in message
...
Thank Bob Phillips
As I key in following code

=workday(A30,INT(A29/8))+MOD(A30,1)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/
24)13/24)/24)+(N((MOD(A30,1)+MOD(A29,8)/24)17/24)/24),
then it displays #NAME?
There is something wrong with workday function, do you have any idea on
how
to fix it?

Thank you very much
Eric


"Bob Phillips" wrote:

Eric,

Try this


=workday(A30,INT(A29/8))+MOD(A30,1)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/
24)13/24)/24)+(N((MOD(A30,1)+MOD(A29,8)/24)17/24)/24)

This assumes weekdays only. IF you want to include Sat anmd Sun, use


=A30+INT(A29/8)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/24)13/24)/24)+(N((M
OD(A30,1)+MOD(A29,8)/24)17/24)/24)

Both assume the start date/time in A30, the number of hours in A29

--

HTH

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


"Eric" wrote in message
...
Does anyone know how to project the due date according to a required
number
of working hours? such as

If today is 24 Oct, 2005 (Monday) at 9:00am, my project required 100
hours
to be finished, then how to create a formula and to project the due
date
in
excel sheet?

Working hour: 9am - 1pm and 2pm - 5pm (total 8 working hour per day)

Thank for any suggestion
Eric

Hi Bob Phillips:
Could you please tell me where I can find the toolpak add-in? when I
install office, I include everything under Microsoft Office - Microsoft
Excel for Windows and other solutions are included with default setting.
Could you please tell me how to load WORKDAY feature?
Thank you very much
Eric

"Bob Phillips" wrote:

WORKDAY is part of the analysis toolpak add-in. Make sure you have that
installed.

--

HTH

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


"Eric" wrote in message
...
Thank Bob Phillips
As I key in following code

=workday(A30,INT(A29/8))+MOD(A30,1)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/
24)13/24)/24)+(N((MOD(A30,1)+MOD(A29,8)/24)17/24)/24),
then it displays #NAME?
There is something wrong with workday function, do you have any idea on
how
to fix it?

Thank you very much
Eric


"Bob Phillips" wrote:

Eric,

Try this


=workday(A30,INT(A29/8))+MOD(A30,1)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/
24)13/24)/24)+(N((MOD(A30,1)+MOD(A29,8)/24)17/24)/24)

This assumes weekdays only. IF you want to include Sat anmd Sun, use


=A30+INT(A29/8)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/24)13/24)/24)+(N((M
OD(A30,1)+MOD(A29,8)/24)17/24)/24)

Both assume the start date/time in A30, the number of hours in A29

--

HTH

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


"Eric" wrote in message
...
Does anyone know how to project the due date according to a required
number
of working hours? such as

If today is 24 Oct, 2005 (Monday) at 9:00am, my project required 100
hours
to be finished, then how to create a formula and to project the due
date
in
excel sheet?

Working hour: 9am - 1pm and 2pm - 5pm (total 8 working hour per day)

Thank for any suggestion
Eric

Hi Bob Phillips:
Please ignore the toolpak problem, I have solved it.

=workday(A30,INT(A29/8))+MOD(A30,1)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/24)13/24)/24)+(N((MOD(A30,1)+MOD(A29,8)/24)17/24)/24)

Thank for your code, could you please give me any suggestion on how to do
it? If I may start counting the hours at 11am, 3pm, ... according to the
starting date.
The above code is assumed that I always start at 9 am.

Thank you very much
Eric


"Bob Phillips" wrote:

Eric,

Try this

=workday(A30,INT(A29/8))+MOD(A30,1)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/
24)13/24)/24)+(N((MOD(A30,1)+MOD(A29,8)/24)17/24)/24)

This assumes weekdays only. IF you want to include Sat anmd Sun, use

=A30+INT(A29/8)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/24)13/24)/24)+(N((M
OD(A30,1)+MOD(A29,8)/24)17/24)/24)

Both assume the start date/time in A30, the number of hours in A29

--

HTH

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


"Eric" wrote in message
...
Does anyone know how to project the due date according to a required
number
of working hours? such as

If today is 24 Oct, 2005 (Monday) at 9:00am, my project required 100 hours
to be finished, then how to create a formula and to project the due date
in
excel sheet?

Working hour: 9am - 1pm and 2pm - 5pm (total 8 working hour per day)

Thank for any suggestion
Eric

Go to ToolsAddins, and it should be near the top of the list. Check it.
Workday is part of ATP, so nothing else needed.

--

HTH

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


"Eric" wrote in message
...
Hi Bob Phillips:
Could you please tell me where I can find the toolpak add-in? when I
install office, I include everything under Microsoft Office - Microsoft
Excel for Windows and other solutions are included with default setting.
Could you please tell me how to load WORKDAY feature?
Thank you very much
Eric

"Bob Phillips" wrote:

WORKDAY is part of the analysis toolpak add-in. Make sure you have that
installed.

--

HTH

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


"Eric" wrote in message
...
Thank Bob Phillips
As I key in following code


=workday(A30,INT(A29/8))+MOD(A30,1)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/
24)13/24)/24)+(N((MOD(A30,1)+MOD(A29,8)/24)17/24)/24),
then it displays #NAME?
There is something wrong with workday function, do you have any idea
on
how
to fix it?

Thank you very much
Eric


"Bob Phillips" wrote:

Eric,

Try this



=workday(A30,INT(A29/8))+MOD(A30,1)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/
24)13/24)/24)+(N((MOD(A30,1)+MOD(A29,8)/24)17/24)/24)

This assumes weekdays only. IF you want to include Sat anmd Sun, use



=A30+INT(A29/8)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/24)13/24)/24)+(N((M
OD(A30,1)+MOD(A29,8)/24)17/24)/24)

Both assume the start date/time in A30, the number of hours in A29

--

HTH

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


"Eric" wrote in message
...
Does anyone know how to project the due date according to a
required
number
of working hours? such as

If today is 24 Oct, 2005 (Monday) at 9:00am, my project required
100
hours
to be finished, then how to create a formula and to project the
due
date
in
excel sheet?

Working hour: 9am - 1pm and 2pm - 5pm (total 8 working hour per
day)

Thank for any suggestion
Eric

It doesn't take account of 09:00 per se, but rather the 13:00 break time .
So if you want to start at 11:00 and break 1t 15:00, change the 13/24 to
15/24.

BTW, the formula doesn't need the end bit, just use

=workday(A30,INT(A29/8))+MOD(A30,1)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/
24)13/24)/24)

--

HTH

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


"Eric" wrote in message
...
Hi Bob Phillips:
Please ignore the toolpak problem, I have solved it.


=workday(A30,INT(A29/8))+MOD(A30,1)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/
24)13/24)/24)+(N((MOD(A30,1)+MOD(A29,8)/24)17/24)/24)

Thank for your code, could you please give me any suggestion on how to do
it? If I may start counting the hours at 11am, 3pm, ... according to the
starting date.
The above code is assumed that I always start at 9 am.

Thank you very much
Eric


"Bob Phillips" wrote:

Eric,

Try this


=workday(A30,INT(A29/8))+MOD(A30,1)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/
24)13/24)/24)+(N((MOD(A30,1)+MOD(A29,8)/24)17/24)/24)

This assumes weekdays only. IF you want to include Sat anmd Sun, use


=A30+INT(A29/8)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/24)13/24)/24)+(N((M
OD(A30,1)+MOD(A29,8)/24)17/24)/24)

Both assume the start date/time in A30, the number of hours in A29

--

HTH

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


"Eric" wrote in message
...
Does anyone know how to project the due date according to a required
number
of working hours? such as

If today is 24 Oct, 2005 (Monday) at 9:00am, my project required 100
hours
to be finished, then how to create a formula and to project the due
date
in
excel sheet?

Working hour: 9am - 1pm and 2pm - 5pm (total 8 working hour per day)

Thank for any suggestion
Eric

Thank you very much
Eric

"Bob Phillips" wrote:

It doesn't take account of 09:00 per se, but rather the 13:00 break time .
So if you want to start at 11:00 and break 1t 15:00, change the 13/24 to
15/24.

BTW, the formula doesn't need the end bit, just use

=workday(A30,INT(A29/8))+MOD(A30,1)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/
24)13/24)/24)

--

HTH

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


"Eric" wrote in message
...
Hi Bob Phillips:
Please ignore the toolpak problem, I have solved it.


=workday(A30,INT(A29/8))+MOD(A30,1)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/
24)13/24)/24)+(N((MOD(A30,1)+MOD(A29,8)/24)17/24)/24)

Thank for your code, could you please give me any suggestion on how to do
it? If I may start counting the hours at 11am, 3pm, ... according to the
starting date.
The above code is assumed that I always start at 9 am.

Thank you very much
Eric


"Bob Phillips" wrote:

Eric,

Try this


=workday(A30,INT(A29/8))+MOD(A30,1)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/
24)13/24)/24)+(N((MOD(A30,1)+MOD(A29,8)/24)17/24)/24)

This assumes weekdays only. IF you want to include Sat anmd Sun, use


=A30+INT(A29/8)+MOD(A29,8)/24+(N((MOD(A30,1)+MOD(A29,8)/24)13/24)/24)+(N((M
OD(A30,1)+MOD(A29,8)/24)17/24)/24)

Both assume the start date/time in A30, the number of hours in A29

--

HTH

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


"Eric" wrote in message
...
Does anyone know how to project the due date according to a required
number
of working hours? such as

If today is 24 Oct, 2005 (Monday) at 9:00am, my project required 100
hours
to be finished, then how to create a formula and to project the due
date
in
excel sheet?

Working hour: 9am - 1pm and 2pm - 5pm (total 8 working hour per day)

Thank for any suggestion
Eric