Work Hours Time Question
 

I have a date and time in cell a1-a100 i need a formula in b2 that will
provide me with the date and time it will be 8 hours later(deadline). the
deadline only counts for work hours between 9am and 9pm during workdays
mon-fri only. thanks.

=IF((A1+(8/24))-INT(A1+(8/24))0.875,A1+(8/24)+0.5,A1+(8/24))

"Qaspec" wrote:

I have a date and time in cell a1-a100 i need a formula in b2 that will
provide me with the date and time it will be 8 hours later(deadline). the
deadline only counts for work hours between 9am and 9pm during workdays
mon-fri only. thanks.

That takes care of the 9 am to 9 pm issue but the workday only stipulation is
still mising. a deadline cannot fall on a weekend.

"LanceB" wrote:

=IF((A1+(8/24))-INT(A1+(8/24))0.875,A1+(8/24)+0.5,A1+(8/24))

"Qaspec" wrote:

I have a date and time in cell a1-a100 i need a formula in b2 that will
provide me with the date and time it will be 8 hours later(deadline). the
deadline only counts for work hours between 9am and 9pm during workdays
mon-fri only. thanks.

Hi,

Assuming:

A1: Start TimeStamp (date+time)
B1: task duration time
holidays: a range of holidays (this is optional)

C4: BegShift (9:00:00 in your case)
C5: EndShift (21:00:00 in your case)

The following gives the ending time of that task:

=WORKDAY(A1,ROUND(B1/(C5-C4),3)+N(MOD(A1,1)(C5-MOD(B1,C5-C4))),holidays)+(MOD(A
1,1)+MOD(B1,C5-C4)+IF(MOD(A1,1)C5-MOD(B1,C5-C4),C4-C5))

Regards,

Daniel M.

"Qaspec" wrote in message
...
That takes care of the 9 am to 9 pm issue but the workday only stipulation is
still mising. a deadline cannot fall on a weekend.

"LanceB" wrote:

=IF((A1+(8/24))-INT(A1+(8/24))0.875,A1+(8/24)+0.5,A1+(8/24))

"Qaspec" wrote:

I have a date and time in cell a1-a100 i need a formula in b2 that will
provide me with the date and time it will be 8 hours later(deadline). the
deadline only counts for work hours between 9am and 9pm during workdays
mon-fri only. thanks.

This doesn't work quite right.

If I enter a start date - time of 1/1/2005 14:00 and a
time duration of 8:00 the result is 1/3/2005 10:00.
Shouldn't the result be 1/3/2005 17:00 ? Did not use
holidays in the formula.

1/1/2005 - Sat
1/2/2005 - Sun

Excluding weekends and only using a time range of 9:00 AM
to 9:00 PM for weekdays. So the time duration should start
on 1/3/2005 9:00 AM and end on 1/3/2005 5:00 PM.


-----Original Message-----
Hi,

Assuming:

A1: Start TimeStamp (date+time)
B1: task duration time
holidays: a range of holidays (this is optional)

C4: BegShift (9:00:00 in your case)
C5: EndShift (21:00:00 in your case)

The following gives the ending time of that task:

=WORKDAY(A1,ROUND(B1/(C5-C4),3)+N(MOD(A1,1)(C5-MOD(B1,C5-
C4))),holidays)+(MOD(A
1,1)+MOD(B1,C5-C4)+IF(MOD(A1,1)C5-MOD(B1,C5-C4),C4-C5))

Regards,

Daniel M.

"Qaspec" wrote in
message
...
That takes care of the 9 am to 9 pm issue but the
workday only stipulation is
still mising. a deadline cannot fall on a weekend.

"LanceB" wrote:

=IF((A1+(8/24))-INT(A1+(8/24))0.875,A1+(8/24)+0.5,A1+
(8/24))

"Qaspec" wrote:

I have a date and time in cell a1-a100 i need a
formula in b2 that will
provide me with the date and time it will be 8
hours later(deadline). the
deadline only counts for work hours between 9am
and 9pm during workdays
mon-fri only. thanks.


.

Hi,

It will fail whenever your StartTimeStamp (A1) is already in non-working hours.

For traditional projects, you always start/assign (also end/stop) work at a
working period.

The formula can probably be adapted to handle those cases but my guess is it
would generate quite a big formula (probably better to be handled via VBA then).

One final note: I think there are some dedicated tools (project scheduling à la
MS Project) that handle those needs much better than Excel is.

Regards,

Daniel M.

"anonymous" wrote in message
...
This doesn't work quite right.

If I enter a start date - time of 1/1/2005 14:00 and a
time duration of 8:00 the result is 1/3/2005 10:00.
Shouldn't the result be 1/3/2005 17:00 ? Did not use
holidays in the formula.

1/1/2005 - Sat
1/2/2005 - Sun

Excluding weekends and only using a time range of 9:00 AM
to 9:00 PM for weekdays. So the time duration should start
on 1/3/2005 9:00 AM and end on 1/3/2005 5:00 PM.


-----Original Message-----
Hi,

Assuming:

A1: Start TimeStamp (date+time)
B1: task duration time
holidays: a range of holidays (this is optional)

C4: BegShift (9:00:00 in your case)
C5: EndShift (21:00:00 in your case)

The following gives the ending time of that task:

=WORKDAY(A1,ROUND(B1/(C5-C4),3)+N(MOD(A1,1)(C5-MOD(B1,C5-
C4))),holidays)+(MOD(A
1,1)+MOD(B1,C5-C4)+IF(MOD(A1,1)C5-MOD(B1,C5-C4),C4-C5))

Regards,

Daniel M.

"Qaspec" wrote in
message
...
That takes care of the 9 am to 9 pm issue but the
workday only stipulation is
still mising. a deadline cannot fall on a weekend.

"LanceB" wrote:

=IF((A1+(8/24))-INT(A1+(8/24))0.875,A1+(8/24)+0.5,A1+
(8/24))

"Qaspec" wrote:

I have a date and time in cell a1-a100 i need a
formula in b2 that will
provide me with the date and time it will be 8
hours later(deadline). the
deadline only counts for work hours between 9am
and 9pm during workdays
mon-fri only. thanks.


.