changing time formula from 16 hour days to 24

[Guest]

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,'WORK
STEPS'!D$2:E$39,2,TRUE))>0.041666),MOD(D20,1)<0.333333),IF(WEEKDAY(D20,1)=6,(ROUNDDOWN(D20,0)+2.333333+VLOOKUP(A21,'WORK
STEPS'!D$2:E$39,2,TRUE)),(ROUNDDOWN(D20,0)+0.333333+VLOOKUP(A21,'WORK
STEPS'!D$2:E$39,2,TRUE))),D20+VLOOKUP(A21,'WORK STEPS'!D$2:E$39,2,TRUE)))


Thank You

Scott

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

and D20 ?!
--
p45cal

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

 

[Guest]

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

 

[Guest]

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?

 

[Guest]

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

 

[Guest]

I made a mistake when I said that the formula you gave me ended the week Sat
1am and started on Mon 8am. On testing on my PC with XL2003 the week ends Fri
1am and starts Sun 8am. Do you run this on Excel on a Mac?

The following formula uses Sat 1am as the end of the working week and Mon
8am as the start. If it uses the wrong days for the weekend on your system,
you may have to change the =7 back to =6.

It seems to work well on cursory testing, BUT YOU WILL HAVE TO CHECK IT OUT
THOROUGHLY YOURSELF. This belongs in cell D21:

=IF(A21="","",IF(AND((MOD(D20,1)+(VLOOKUP(A21,'WORK
STEPS'!D$2:E$39,2,TRUE))>0.041666),MOD(D20,1)<0.333333,WEEKDAY(D20,1)=7),ROUNDDOWN(D20,0)+2.333333+VLOOKUP(A21,'WORK
STEPS'!D$2:E$39,2,TRUE),D20+VLOOKUP(A21,'WORK STEPS'!D$2:E$39,2,TRUE)))

and can be filled down and/or up the column.

ps. You gave very little information out, I had to work out or make
assumptions for myself about where things were, what kind of data it was.
Normally, on viewing posts with such sparse information I would have passed
onto another question, but unusually, I had time to kill. If you can't be
bothered to supply full details, why should others be bothered to look at
your problem? Help others to help you. OK, lecture over, hope this fits the
bill.

 

[Guest]

p45cal,

Your awesome. Thanks for all of your help. This was actually the second time
I posted a question and I tried to review several other questions before I
sent mine out so that I could try and determine what to send. I will keep
your inputs in mind when asking another question.


Great help

Scott

PS I use this on excel

 

[Guest]

p45cal,

Your formula works great thanks again. Got one more question if you do not
mind. With this spreedsheet I am not only tracking how much time is required
to complete all of the steps and when they should be finished with one step
and on to the next, but I am also tracking when the process was started and
when the parts are due.

Customer
File # P11672
Due Date: 7/18/07
Released: 7/12/07 8:17 AM
Workdays: #VALUE!

My problem is I get an Value error.

In the released colum I am using =Now() and in the work days I am using
=NETWORKDAYS(C5,D10,NETWORKDAYS!C1:C10)
C5 is the due date, D10 is the first process step and NETWORKDAYS is another
work sheet that has all of the holidays.

Hope this enough information

Regards

Scott

 

[Guest]

The first thing I'd do is change the name of the sheet holding the holidays.
Calling it NETWORKDAYS is the using the same name as the function which is
confusing, at least to anyone else looking at the code, if not to the
computer. Call it something else and reconstruct the formula from scratch. If
it still comes back with #VALUE then post again.

 

[Guest]

p45cal


The work sheet is called holidays. I am using the NETWORKDAY function so the
formula is =NETWORKDAYS(D10,C5,HOLIDAYS!D123)


D10 is the start date, C5 is the due date minus any holidays that might
occur during that time gives me X# of days to complete. I actually have
gotten the formula to work but have to input the due date in backwards to
make it work. 2007/07/14.

Also I am trying to get the time required to process to change based on the
number of parts that we are processing. I have added the number of parts and
have put in the factor based on time to process ten parts but some times will
increase by 30 to 100% if the number of parts increase. Any easy way to
account for that?
Code Description Hour Code Factor Parts
10 IL MTL ISSUE 0.50 10 0.02 40
20 IL COAT 0.50 20 0.02 40
30 IL IMAGE 0.50 30 0.02 40
40 DES 0.50 40 0.02 40

Thanks again


Scott