|
changing time formula from 16 hour days to 24
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 |
changing time formula from 16 hour days to 24
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 |
changing time formula from 16 hour days to 24
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 |
changing time formula from 16 hour days to 24
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 |
changing time formula from 16 hour days to 24
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 |
changing time formula from 16 hour days to 24
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 |
changing time formula from 16 hour days to 24
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 |
changing time formula from 16 hour days to 24
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,'WOR K STEPS'!D$2:E$39,2,TRUE))0.041666),MOD(D20,1)<0.33 3333,WEEKDAY(D20,1)=7),ROUNDDOWN(D20,0)+2.333333+V LOOKUP(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. -- p45cal "Scott W" wrote: 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 |
changing time formula from 16 hour days to 24
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 "p45cal" wrote: 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,'WOR K STEPS'!D$2:E$39,2,TRUE))0.041666),MOD(D20,1)<0.33 3333,WEEKDAY(D20,1)=7),ROUNDDOWN(D20,0)+2.333333+V LOOKUP(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. -- p45cal "Scott W" wrote: 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 |
changing time formula from 16 hour days to 24
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 "p45cal" wrote: 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,'WOR K STEPS'!D$2:E$39,2,TRUE))0.041666),MOD(D20,1)<0.33 3333,WEEKDAY(D20,1)=7),ROUNDDOWN(D20,0)+2.333333+V LOOKUP(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. -- p45cal "Scott W" wrote: 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 |
changing time formula from 16 hour days to 24
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. -- p45cal "Scott W" wrote: 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 |
changing time formula from 16 hour days to 24
p45cal
The work sheet is called holidays. I am using the NETWORKDAY function so the formula is =NETWORKDAYS(D10,C5,HOLIDAYS!D1:D23) 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 "p45cal" wrote: 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. -- p45cal "Scott W" wrote: 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 |
| All times are GMT +1. The time now is 10:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com