How to project the due date according to a number of working hours
 

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

How to project the due date according to a number of working hours
 

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

How to project the due date according to a number of working h
 

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

How to project the due date according to a number of working h
 

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

How to project the due date according to a number of working h
 

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

How to project the due date according to a number of working h
 

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

How to project the due date according to a number of working h
 

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

How to project the due date according to a number of working h
 

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

How to project the due date according to a number of working h
 

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