Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
=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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. . |
#6
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time formula question... | Excel Discussion (Misc queries) | |||
Time - Hours and Minutes | Excel Discussion (Misc queries) | |||
calculate difference in time to hours | Excel Worksheet Functions | |||
in excel totaling weekly hours military time | Excel Worksheet Functions | |||
Best time question | Excel Worksheet Functions |