changing time formula from 16 hour days to 24

's Computer Specifications

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

's Computer Specifications

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,'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

's Computer Specifications

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,'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

's Computer Specifications

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,'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

's Computer Specifications

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,'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

's Computer Specifications

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,'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

's Computer Specifications

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,'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

's Computer Specifications

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.

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

's Computer Specifications

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

's Computer Specifications

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

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Changing decimal time into 24 hour time and reverse	Bobzter100	Microsoft Excel Misc	4	25th Jan 2008 11:38 AM
Changing work hour days from 16 to 24	=?Utf-8?B?U2NvdHQgVw==?=	Microsoft Excel Programming	1	11th Jul 2007 07:08 PM
Changing time display from 12 to 24 hour time possible?	=?Utf-8?B?SmF4?=	Windows XP Help	1	8th Jun 2007 01:04 AM
Time calculations and additional 24 hour days	=?Utf-8?B?TWFyayBH?=	Microsoft Excel Worksheet Functions	2	29th Dec 2005 08:39 PM
HELP!need to determine price based on time to complete using Min. Hour. And days	Tiffany	Microsoft Excel Worksheet Functions	10	24th Feb 2004 08:04 PM