I am using a spreed sheet that runs a time line as to how long something will
take to be produced. On a separate work sheet I have numbered the different
step and assigned how much time it takes to go through each step. All someone
needs to do is enter in all of the steps that a part will go through and it
calculates how long it will take. My problem is that the formulas are based
on a 16 hour work day and not 24 hours. How can I change this?

Here is the formula

=IF(A21="","",IF(AND((MOD(D20,1)+(VLOOKUP(A21,'WOR K
STEPS'!D$2:E$39,2,TRUE))0.041666),MOD(D20,1)<0.33 3333),IF(WEEKDAY(D20,1)=6,(ROUNDDOWN(D20,0)+2.3333 33+VLOOKUP(A21,'WORK
STEPS'!D$2:E$39,2,TRUE)),(ROUNDDOWN(D20,0)+0.33333 3+VLOOKUP(A21,'WORK
STEPS'!D$2:E$39,2,TRUE))),D20+VLOOKUP(A21,'WORK STEPS'!D$2:E$39,2,TRUE)))


Thank You

Scott

The only things that seem to relate to time in hours is the value 0.041666
which is 1 hour, and 0.33333 which is 8 hours. Without knowing what's in the
range 'WORK STEPS'!D$2:E$39, and what's in A21, D20, I doubt that anyone's
going to be willing or able to help. Maybe just a smidgin more information.
--
p45cal


"Scott W" wrote:

I am using a spreed sheet that runs a time line as to how long something will
take to be produced. On a separate work sheet I have numbered the different
step and assigned how much time it takes to go through each step. All someone
needs to do is enter in all of the steps that a part will go through and it
calculates how long it will take. My problem is that the formulas are based
on a 16 hour work day and not 24 hours. How can I change this?

Here is the formula

=IF(A21="","",IF(AND((MOD(D20,1)+(VLOOKUP(A21,'WOR K
STEPS'!D$2:E$39,2,TRUE))0.041666),MOD(D20,1)<0.33 3333),IF(WEEKDAY(D20,1)=6,(ROUNDDOWN(D20,0)+2.3333 33+VLOOKUP(A21,'WORK
STEPS'!D$2:E$39,2,TRUE)),(ROUNDDOWN(D20,0)+0.33333 3+VLOOKUP(A21,'WORK
STEPS'!D$2:E$39,2,TRUE))),D20+VLOOKUP(A21,'WORK STEPS'!D$2:E$39,2,TRUE)))


Thank You

Scott

In the work steps I have the foloowing information


Step Discription time code Factor
10 Issue Material 0.05 10 .02
20 Clean 0.05 20 .02
30 Bake 1.00 30 .04


In A21 it has the setp and D21 has the formula that I sent earlier.


Thanks


Scott

"Scott W" wrote:

I am using a spreed sheet that runs a time line as to how long something will
take to be produced. On a separate work sheet I have numbered the different
step and assigned how much time it takes to go through each step. All someone
needs to do is enter in all of the steps that a part will go through and it
calculates how long it will take. My problem is that the formulas are based
on a 16 hour work day and not 24 hours. How can I change this?

Here is the formula

=IF(A21="","",IF(AND((MOD(D20,1)+(VLOOKUP(A21,'WOR K
STEPS'!D$2:E$39,2,TRUE))0.041666),MOD(D20,1)<0.33 3333),IF(WEEKDAY(D20,1)=6,(ROUNDDOWN(D20,0)+2.3333 33+VLOOKUP(A21,'WORK
STEPS'!D$2:E$39,2,TRUE)),(ROUNDDOWN(D20,0)+0.33333 3+VLOOKUP(A21,'WORK
STEPS'!D$2:E$39,2,TRUE))),D20+VLOOKUP(A21,'WORK STEPS'!D$2:E$39,2,TRUE)))


Thank You

Scott

and D20 ?!
--
p45cal


"Scott W" wrote:

In the work steps I have the foloowing information


Step Discription time code Factor
10 Issue Material 0.05 10 .02
20 Clean 0.05 20 .02
30 Bake 1.00 30 .04


In A21 it has the setp and D21 has the formula that I sent earlier.


Thanks


Scott

"Scott W" wrote:

I am using a spreed sheet that runs a time line as to how long something will
take to be produced. On a separate work sheet I have numbered the different
step and assigned how much time it takes to go through each step. All someone
needs to do is enter in all of the steps that a part will go through and it
calculates how long it will take. My problem is that the formulas are based
on a 16 hour work day and not 24 hours. How can I change this?

Here is the formula

=IF(A21="","",IF(AND((MOD(D20,1)+(VLOOKUP(A21,'WOR K
STEPS'!D$2:E$39,2,TRUE))0.041666),MOD(D20,1)<0.33 3333),IF(WEEKDAY(D20,1)=6,(ROUNDDOWN(D20,0)+2.3333 33+VLOOKUP(A21,'WORK
STEPS'!D$2:E$39,2,TRUE)),(ROUNDDOWN(D20,0)+0.33333 3+VLOOKUP(A21,'WORK
STEPS'!D$2:E$39,2,TRUE))),D20+VLOOKUP(A21,'WORK STEPS'!D$2:E$39,2,TRUE)))


Thank You

Scott

Here is the actual time line sheet


Colum A B C D


Step # Description Move BY
10 IL MTL ISSUE 7/11/07 12:47 PM
20 IL COAT 7/11/07 1:17 PM
30 IL IMAGE 7/11/07 1:47 PM

Work Step Sheet

Colum A B C
Code Description Hour Code
Factor
10 IL MTL ISSUE 0.05 10 0 .020
20 Clean 0.05 20
0.020
30 Bake 1.50 30 0.020


Etc. I also have it linked to another work sheet which has holidys. Right
now since it is set up for 16 hour work days when it comes to 12:00AM the
next step does not start until 8:00am


Thanks
"p45cal" wrote:

and D20 ?!
--
p45cal


"Scott W" wrote:

In the work steps I have the foloowing information


Step Discription time code Factor
10 Issue Material 0.05 10 .02
20 Clean 0.05 20 .02
30 Bake 1.00 30 .04


In A21 it has the setp and D21 has the formula that I sent earlier.


Thanks


Scott

"Scott W" wrote:

I am using a spreed sheet that runs a time line as to how long something will
take to be produced. On a separate work sheet I have numbered the different
step and assigned how much time it takes to go through each step. All someone
needs to do is enter in all of the steps that a part will go through and it
calculates how long it will take. My problem is that the formulas are based
on a 16 hour work day and not 24 hours. How can I change this?

Here is the formula

=IF(A21="","",IF(AND((MOD(D20,1)+(VLOOKUP(A21,'WOR K
STEPS'!D$2:E$39,2,TRUE))0.041666),MOD(D20,1)<0.33 3333),IF(WEEKDAY(D20,1)=6,(ROUNDDOWN(D20,0)+2.3333 33+VLOOKUP(A21,'WORK
STEPS'!D$2:E$39,2,TRUE)),(ROUNDDOWN(D20,0)+0.33333 3+VLOOKUP(A21,'WORK
STEPS'!D$2:E$39,2,TRUE))),D20+VLOOKUP(A21,'WORK STEPS'!D$2:E$39,2,TRUE)))


Thank You

Scott

Right, the formula you gave allows steps to go on until 1am; if they would go
beyond 1am then that step starts at 8am next morning instead. A 17 hour day.
The formula you gave also takes into account weekends, with the last job
having to be finished before Saturday 1am, Monday 8am being the next start
time.

Do you still want weekends taken into acount? If so, is the end of the week
still 1am Saturday, and start of the week 8am Monday? Or is production now
truly 24/7?

--
p45cal


"Scott W" wrote:

Here is the actual time line sheet


Colum A B C D


Step # Description Move BY
10 IL MTL ISSUE 7/11/07 12:47 PM
20 IL COAT 7/11/07 1:17 PM
30 IL IMAGE 7/11/07 1:47 PM

Work Step Sheet

Colum A B C
Code Description Hour Code
Factor
10 IL MTL ISSUE 0.05 10 0 .020
20 Clean 0.05 20
0.020
30 Bake 1.50 30 0.020


Etc. I also have it linked to another work sheet which has holidys. Right
now since it is set up for 16 hour work days when it comes to 12:00AM the
next step does not start until 8:00am


Thanks
"p45cal" wrote:

and D20 ?!
--
p45cal


"Scott W" wrote:

In the work steps I have the foloowing information


Step Discription time code Factor
10 Issue Material 0.05 10 .02
20 Clean 0.05 20 .02
30 Bake 1.00 30 .04


In A21 it has the setp and D21 has the formula that I sent earlier.


Thanks


Scott

"Scott W" wrote:

I am using a spreed sheet that runs a time line as to how long something will
take to be produced. On a separate work sheet I have numbered the different
step and assigned how much time it takes to go through each step. All someone
needs to do is enter in all of the steps that a part will go through and it
calculates how long it will take. My problem is that the formulas are based
on a 16 hour work day and not 24 hours. How can I change this?

Here is the formula

=IF(A21="","",IF(AND((MOD(D20,1)+(VLOOKUP(A21,'WOR K
STEPS'!D$2:E$39,2,TRUE))0.041666),MOD(D20,1)<0.33 3333),IF(WEEKDAY(D20,1)=6,(ROUNDDOWN(D20,0)+2.3333 33+VLOOKUP(A21,'WORK
STEPS'!D$2:E$39,2,TRUE)),(ROUNDDOWN(D20,0)+0.33333 3+VLOOKUP(A21,'WORK
STEPS'!D$2:E$39,2,TRUE))),D20+VLOOKUP(A21,'WORK STEPS'!D$2:E$39,2,TRUE)))


Thank You

Scott

I do need to go 24 hours per day but only until Saturday 1:00Am and start
again on Monday at 8:00AM. I really appreciate your help.

Thanks

Scott

"p45cal" wrote:

Right, the formula you gave allows steps to go on until 1am; if they would go
beyond 1am then that step starts at 8am next morning instead. A 17 hour day.
The formula you gave also takes into account weekends, with the last job
having to be finished before Saturday 1am, Monday 8am being the next start
time.

Do you still want weekends taken into acount? If so, is the end of the week
still 1am Saturday, and start of the week 8am Monday? Or is production now
truly 24/7?

--
p45cal


"Scott W" wrote:

Here is the actual time line sheet


Colum A B C D


Step # Description Move BY
10 IL MTL ISSUE 7/11/07 12:47 PM
20 IL COAT 7/11/07 1:17 PM
30 IL IMAGE 7/11/07 1:47 PM

Work Step Sheet

Colum A B C
Code Description Hour Code
Factor
10 IL MTL ISSUE 0.05 10 0 .020
20 Clean 0.05 20
0.020
30 Bake 1.50 30 0.020


Etc. I also have it linked to another work sheet which has holidys. Right
now since it is set up for 16 hour work days when it comes to 12:00AM the
next step does not start until 8:00am


Thanks
"p45cal" wrote:

and D20 ?!
--
p45cal


"Scott W" wrote:

In the work steps I have the foloowing information


Step Discription time code Factor
10 Issue Material 0.05 10 .02
20 Clean 0.05 20 .02
30 Bake 1.00 30 .04


In A21 it has the setp and D21 has the formula that I sent earlier.


Thanks


Scott

"Scott W" wrote:

I am using a spreed sheet that runs a time line as to how long something will
take to be produced. On a separate work sheet I have numbered the different
step and assigned how much time it takes to go through each step. All someone
needs to do is enter in all of the steps that a part will go through and it
calculates how long it will take. My problem is that the formulas are based
on a 16 hour work day and not 24 hours. How can I change this?

Here is the formula

=IF(A21="","",IF(AND((MOD(D20,1)+(VLOOKUP(A21,'WOR K
STEPS'!D$2:E$39,2,TRUE))0.041666),MOD(D20,1)<0.33 3333),IF(WEEKDAY(D20,1)=6,(ROUNDDOWN(D20,0)+2.3333 33+VLOOKUP(A21,'WORK
STEPS'!D$2:E$39,2,TRUE)),(ROUNDDOWN(D20,0)+0.33333 3+VLOOKUP(A21,'WORK
STEPS'!D$2:E$39,2,TRUE))),D20+VLOOKUP(A21,'WORK STEPS'!D$2:E$39,2,TRUE)))


Thank You

Scott