+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Eric
    Guest

    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

  2. #2
    Bob Phillips
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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




  3. #3
    Eric
    Guest

    Re: 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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

    >
    >
    >


  4. #4
    Bob Phillips
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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

    > >
    > >
    > >




  5. #5
    Eric
    Guest

    Re: 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > 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
    > > >
    > > >
    > > >

    >
    >
    >


  6. #6
    Eric
    Guest

    Re: 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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

    >
    >
    >


  7. #7
    Bob Phillips
    Guest

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

    Go to Tools>Addins, 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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" <[email protected]> wrote in message
    > > > > news:[email protected]...
    > > > > > 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
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >




  8. #8
    Bob Phillips
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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

    > >
    > >
    > >




  9. #9
    Eric
    Guest

    Re: 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > 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
    > > >
    > > >
    > > >

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1