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.
.
|