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

Assume starting date/time (e.g. now) is in cell A1, number of hours to work
is in A2:

Completion date formula:
=WORKDAY(A1,INT(A2/8))
Completion time (if you want it):
=A1-INT(A1)+(A2-8*INT(A2/8))/24
explanation: A1-INT(A1) gives the time (decimal) portion of a date/time value.
INT(A2/8) gives the integer number of days in the work hours
A2 - (integer days) gives the number of extra hours to work
Divide that by 24 to turn it into a decimal amount equivalent to 1 hour in
Excel's equivalent date/time value (1 = 1 day, so hours/24 = fractional day)
--
- K Dales


"Eric" wrote:

> 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

I missed one part of the problem:
If the finish time is after 1 pm, we need to add an hour (to account for the
lunch hour in the working day):
=IF(A1-INT(A1)+(A2-8*INT(A2/8))/24>13/24,A1-INT(A1)+(A2-8*INT(A2/8))/24+1,A1-INT(A1)+(A2-8*INT(A2/8))/24)
--
- K Dales


"K Dales" wrote:

> Assume starting date/time (e.g. now) is in cell A1, number of hours to work
> is in A2:
>
> Completion date formula:
> =WORKDAY(A1,INT(A2/8))
> Completion time (if you want it):
> =A1-INT(A1)+(A2-8*INT(A2/8))/24
> explanation: A1-INT(A1) gives the time (decimal) portion of a date/time value.
> INT(A2/8) gives the integer number of days in the work hours
> A2 - (integer days) gives the number of extra hours to work
> Divide that by 24 to turn it into a decimal amount equivalent to 1 hour in
> Excel's equivalent date/time value (1 = 1 day, so hours/24 = fractional day)
> --
> - K Dales
>
>
> "Eric" wrote:
>
> > 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

One more time (yikes!) - in my haste I forgot to divide the extra hour by 24
=IF(A1-INT(A1)+(A2-8*INT(A2/8))/24>13/24,A1-INT(A1)+(A2-8*INT(A2/8))/24+1/24,A1-INT(A1)+(A2-8*INT(A2/8))/24)
--
- K Dales


"K Dales" wrote:

> Assume starting date/time (e.g. now) is in cell A1, number of hours to work
> is in A2:
>
> Completion date formula:
> =WORKDAY(A1,INT(A2/8))
> Completion time (if you want it):
> =A1-INT(A1)+(A2-8*INT(A2/8))/24
> explanation: A1-INT(A1) gives the time (decimal) portion of a date/time value.
> INT(A2/8) gives the integer number of days in the work hours
> A2 - (integer days) gives the number of extra hours to work
> Divide that by 24 to turn it into a decimal amount equivalent to 1 hour in
> Excel's equivalent date/time value (1 = 1 day, so hours/24 = fractional day)
> --
> - K Dales
>
>
> "Eric" wrote:
>
> > 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 K Dales
A1 = 2005/1/3 11:00 AM
A2 = 100
According to following code
=IF(A1-INT(A1)+(A2-8*INT(A2/8))/24>13/24,A1-INT(A1)+(A2-8*INT(A2/8))/24+1/24,A1-INT(A1)+(A2-8*INT(A2/8))/24)
The result is shown below
=1900/1/0 4:00 PM
There is something wrong with the date, do you know how to fix it?

Furthermore, I don't know what wrong it is, once I type following code
=WORKDAY(A1,INT(A2/8)), then it displays #NAME?
Do you have any idea how to fix it too?

Thank you very much
Eric

"K Dales" wrote:

> One more time (yikes!) - in my haste I forgot to divide the extra hour by 24
> =IF(A1-INT(A1)+(A2-8*INT(A2/8))/24>13/24,A1-INT(A1)+(A2-8*INT(A2/8))/24+1/24,A1-INT(A1)+(A2-8*INT(A2/8))/24)
> --
> - K Dales
>
>
> "K Dales" wrote:
>
> > Assume starting date/time (e.g. now) is in cell A1, number of hours to work
> > is in A2:
> >
> > Completion date formula:
> > =WORKDAY(A1,INT(A2/8))
> > Completion time (if you want it):
> > =A1-INT(A1)+(A2-8*INT(A2/8))/24
> > explanation: A1-INT(A1) gives the time (decimal) portion of a date/time value.
> > INT(A2/8) gives the integer number of days in the work hours
> > A2 - (integer days) gives the number of extra hours to work
> > Divide that by 24 to turn it into a decimal amount equivalent to 1 hour in
> > Excel's equivalent date/time value (1 = 1 day, so hours/24 = fractional day)
> > --
> > - K Dales
> >
> >
> > "Eric" wrote:
> >
> > > 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: sorry it took a while, just noticed this post again

For the first part (completion time formula); it may seem strange but the
result is as I intended. I was only trying to calculate the time portion,
and my intention (though I apologize for not making this clear) was to format
this in the cell as a time value. My "completion date formula" was in one
cell to show the date due, and then I was showing time due in the next cell
over. Adding the two formulas together should give the overall result.

In Excel, all date/time values are actually numeric with the integer part
being days and the fractional part being hours/minutes/seconds within the
day. Day "zero" is January 0, 1900 to Excel. If you add the time to the
date calculated it will give the whole thing in one cell value.

As for the other, it appears to me that Excel is not recognizing the WORKDAY
function. If you pull up the list of functions from the "Paste Function"
button on the toolbar, does this show WORKDAY in the Date function group? Or
if you look in the help file? I use Excel 2000; I am not sure if you use an
earlier version how far back this function was available.
--
- K Dales


"Eric" wrote:

> Thank K Dales
> A1 = 2005/1/3 11:00 AM
> A2 = 100
> According to following code
> =IF(A1-INT(A1)+(A2-8*INT(A2/8))/24>13/24,A1-INT(A1)+(A2-8*INT(A2/8))/24+1/24,A1-INT(A1)+(A2-8*INT(A2/8))/24)
> The result is shown below
> =1900/1/0 4:00 PM
> There is something wrong with the date, do you know how to fix it?
>
> Furthermore, I don't know what wrong it is, once I type following code
> =WORKDAY(A1,INT(A2/8)), then it displays #NAME?
> Do you have any idea how to fix it too?
>
> Thank you very much
> Eric
>
> "K Dales" wrote:
>
> > One more time (yikes!) - in my haste I forgot to divide the extra hour by 24
> > =IF(A1-INT(A1)+(A2-8*INT(A2/8))/24>13/24,A1-INT(A1)+(A2-8*INT(A2/8))/24+1/24,A1-INT(A1)+(A2-8*INT(A2/8))/24)
> > --
> > - K Dales
> >
> >
> > "K Dales" wrote:
> >
> > > Assume starting date/time (e.g. now) is in cell A1, number of hours to work
> > > is in A2:
> > >
> > > Completion date formula:
> > > =WORKDAY(A1,INT(A2/8))
> > > Completion time (if you want it):
> > > =A1-INT(A1)+(A2-8*INT(A2/8))/24
> > > explanation: A1-INT(A1) gives the time (decimal) portion of a date/time value.
> > > INT(A2/8) gives the integer number of days in the work hours
> > > A2 - (integer days) gives the number of extra hours to work
> > > Divide that by 24 to turn it into a decimal amount equivalent to 1 hour in
> > > Excel's equivalent date/time value (1 = 1 day, so hours/24 = fractional day)
> > > --
> > > - K Dales
> > >
> > >
> > > "Eric" wrote:
> > >
> > > > 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 K Dales for your reply
The problem was solved,
Thank you
Eric

"K Dales" wrote:

> Eric: sorry it took a while, just noticed this post again
>
> For the first part (completion time formula); it may seem strange but the
> result is as I intended. I was only trying to calculate the time portion,
> and my intention (though I apologize for not making this clear) was to format
> this in the cell as a time value. My "completion date formula" was in one
> cell to show the date due, and then I was showing time due in the next cell
> over. Adding the two formulas together should give the overall result.
>
> In Excel, all date/time values are actually numeric with the integer part
> being days and the fractional part being hours/minutes/seconds within the
> day. Day "zero" is January 0, 1900 to Excel. If you add the time to the
> date calculated it will give the whole thing in one cell value.
>
> As for the other, it appears to me that Excel is not recognizing the WORKDAY
> function. If you pull up the list of functions from the "Paste Function"
> button on the toolbar, does this show WORKDAY in the Date function group? Or
> if you look in the help file? I use Excel 2000; I am not sure if you use an
> earlier version how far back this function was available.
> --
> - K Dales
>
>
> "Eric" wrote:
>
> > Thank K Dales
> > A1 = 2005/1/3 11:00 AM
> > A2 = 100
> > According to following code
> > =IF(A1-INT(A1)+(A2-8*INT(A2/8))/24>13/24,A1-INT(A1)+(A2-8*INT(A2/8))/24+1/24,A1-INT(A1)+(A2-8*INT(A2/8))/24)
> > The result is shown below
> > =1900/1/0 4:00 PM
> > There is something wrong with the date, do you know how to fix it?
> >
> > Furthermore, I don't know what wrong it is, once I type following code
> > =WORKDAY(A1,INT(A2/8)), then it displays #NAME?
> > Do you have any idea how to fix it too?
> >
> > Thank you very much
> > Eric
> >
> > "K Dales" wrote:
> >
> > > One more time (yikes!) - in my haste I forgot to divide the extra hour by 24
> > > =IF(A1-INT(A1)+(A2-8*INT(A2/8))/24>13/24,A1-INT(A1)+(A2-8*INT(A2/8))/24+1/24,A1-INT(A1)+(A2-8*INT(A2/8))/24)
> > > --
> > > - K Dales
> > >
> > >
> > > "K Dales" wrote:
> > >
> > > > Assume starting date/time (e.g. now) is in cell A1, number of hours to work
> > > > is in A2:
> > > >
> > > > Completion date formula:
> > > > =WORKDAY(A1,INT(A2/8))
> > > > Completion time (if you want it):
> > > > =A1-INT(A1)+(A2-8*INT(A2/8))/24
> > > > explanation: A1-INT(A1) gives the time (decimal) portion of a date/time value.
> > > > INT(A2/8) gives the integer number of days in the work hours
> > > > A2 - (integer days) gives the number of extra hours to work
> > > > Divide that by 24 to turn it into a decimal amount equivalent to 1 hour in
> > > > Excel's equivalent date/time value (1 = 1 day, so hours/24 = fractional day)
> > > > --
> > > > - K Dales
> > > >
> > > >
> > > > "Eric" wrote:
> > > >
> > > > > 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