Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NOTHING WORKS2-HELP
Hi,
I have a column set up with a drop down menu listing the priority of a job(1-5). I have another column set up which gives the predicted finish date of the job as dd/mm/yyyy hh/mm. I have made a function with lookup that gives the predicted finish date when any one of the 1-5 are chosen. 1 priority takes 2 hours 2 4 hours 3 1 day 4 2 days 5 1 week This is the formula i have used: =LOOKUP(J38,{1,2,3,4,5},E38+{"0.083333333333333333 333333333333333","0.166666666666666666666666666666 67","1","2","7"}) How can i adjust it to take into account working days (mon-fri 9am-5pm) Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NOTHING WORKS2-HELP
Try this:
=LOOKUP(I38,{1,2,3,4,5},E38+{0.0833333333333333,0. 166666666666666,1,2,7})+(IF(WEEKDAY(E38)=6,2,IF(WE EKDAY(E38)=7,1,0))) "DaveAsh" wrote: Hi, I have a column set up with a drop down menu listing the priority of a job(1-5). I have another column set up which gives the predicted finish date of the job as dd/mm/yyyy hh/mm. I have made a function with lookup that gives the predicted finish date when any one of the 1-5 are chosen. 1 priority takes 2 hours 2 4 hours 3 1 day 4 2 days 5 1 week This is the formula i have used: =LOOKUP(J38,{1,2,3,4,5},E38+{"0.083333333333333333 333333333333333","0.166666666666666666666666666666 67","1","2","7"}) How can i adjust it to take into account working days (mon-fri 9am-5pm) Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NOTHING WORKS2-HELP
Thanks for the reply, but the formula can still compute answers which are out
of hours. "Mike H." wrote: Try this: =LOOKUP(I38,{1,2,3,4,5},E38+{0.0833333333333333,0. 166666666666666,1,2,7})+(IF(WEEKDAY(E38)=6,2,IF(WE EKDAY(E38)=7,1,0))) "DaveAsh" wrote: Hi, I have a column set up with a drop down menu listing the priority of a job(1-5). I have another column set up which gives the predicted finish date of the job as dd/mm/yyyy hh/mm. I have made a function with lookup that gives the predicted finish date when any one of the 1-5 are chosen. 1 priority takes 2 hours 2 4 hours 3 1 day 4 2 days 5 1 week This is the formula i have used: =LOOKUP(J38,{1,2,3,4,5},E38+{"0.083333333333333333 333333333333333","0.166666666666666666666666666666 67","1","2","7"}) How can i adjust it to take into account working days (mon-fri 9am-5pm) Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NOTHING WORKS2-HELP
"Out of Hours" What does that mean? Are you saying you need to take into
account that people don't work past 5pm daily? Your formula has never taken that into account. Is that what you need to do. For example, if at 4 pm, they have a priority 1, (2hrs), then it would finish at 10 am the next day, assuming both days were weekdays. Let me know. I am sure if this is what you want and I am sure that it can be accomplished. "DaveAsh" wrote: Thanks for the reply, but the formula can still compute answers which are out of hours. "Mike H." wrote: Try this: =LOOKUP(I38,{1,2,3,4,5},E38+{0.0833333333333333,0. 166666666666666,1,2,7})+(IF(WEEKDAY(E38)=6,2,IF(WE EKDAY(E38)=7,1,0))) "DaveAsh" wrote: Hi, I have a column set up with a drop down menu listing the priority of a job(1-5). I have another column set up which gives the predicted finish date of the job as dd/mm/yyyy hh/mm. I have made a function with lookup that gives the predicted finish date when any one of the 1-5 are chosen. 1 priority takes 2 hours 2 4 hours 3 1 day 4 2 days 5 1 week This is the formula i have used: =LOOKUP(J38,{1,2,3,4,5},E38+{"0.083333333333333333 333333333333333","0.166666666666666666666666666666 67","1","2","7"}) How can i adjust it to take into account working days (mon-fri 9am-5pm) Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NOTHING WORKS2-HELP
Yes, sorry for the confusion. I need it to take into account that people
don't work past 5pm daily. Hope you can help. "Mike H." wrote: "Out of Hours" What does that mean? Are you saying you need to take into account that people don't work past 5pm daily? Your formula has never taken that into account. Is that what you need to do. For example, if at 4 pm, they have a priority 1, (2hrs), then it would finish at 10 am the next day, assuming both days were weekdays. Let me know. I am sure if this is what you want and I am sure that it can be accomplished. "DaveAsh" wrote: Thanks for the reply, but the formula can still compute answers which are out of hours. "Mike H." wrote: Try this: =LOOKUP(I38,{1,2,3,4,5},E38+{0.0833333333333333,0. 166666666666666,1,2,7})+(IF(WEEKDAY(E38)=6,2,IF(WE EKDAY(E38)=7,1,0))) "DaveAsh" wrote: Hi, I have a column set up with a drop down menu listing the priority of a job(1-5). I have another column set up which gives the predicted finish date of the job as dd/mm/yyyy hh/mm. I have made a function with lookup that gives the predicted finish date when any one of the 1-5 are chosen. 1 priority takes 2 hours 2 4 hours 3 1 day 4 2 days 5 1 week This is the formula i have used: =LOOKUP(J38,{1,2,3,4,5},E38+{"0.083333333333333333 333333333333333","0.166666666666666666666666666666 67","1","2","7"}) How can i adjust it to take into account working days (mon-fri 9am-5pm) Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NOTHING WORKS2-HELP
I tried to do it as calculation in a cell but it gets too cumbersome. But
this code, which I created in about 10 minutes work fine. The only thing you might want adjusted is that it can end at 5pm the following day if you start at 5 pm the current day. I don't know about that but test it out. If you don't know how to set up this as a macro let me know.... Sub CalcIt() Dim InTime As Double Dim AddTo As Double Dim EndMoment As Double Dim Priority As Integer Dim TheRow As Double Let TheRow = ActiveCell.Row Let InTime = Cells(TheRow, 5) 'E38 Let Priority = Cells(TheRow, 7) 'G38 If Hour(InTime) 17 Then Let InTime = Int(InTime) + (17 / 24) 'sets to 5pm. End If If Priority = 1 Then Let AddTo = 0.083333 ElseIf Priority = 2 Then Let AddTo = 0.166666667 ElseIf Priority = 3 Then Let AddTo = 1 ElseIf Priority = 4 Then Let AddTo = 2 ElseIf Priority = 5 Then Let AddTo = 7 End If Let EndMoment = InTime + AddTo If Hour(EndMoment) 17 Then 'if end time is after 5 pm then must go to next day 'Let AddTo = Hour(EndMoment) - 17 Let EndMoment = Int(EndMoment) Let EndMoment = EndMoment + 1 + (9 / 24) + AddTo 'move to 9 am next day End If If Weekday(EndMoment) = 6 Then Let EndMoment = EndMoment + 2 ElseIf Weekday(EndMoment) = 7 Then Let EndMoment = EndMoment + 1 End If Cells(TheRow, 8).Value = EndMoment End Sub |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NOTHING WORKS2-HELP
Thanks for the time spent on that. I am a novice to macros though so any
directions would be great if possible. Regards "Mike H." wrote: I tried to do it as calculation in a cell but it gets too cumbersome. But this code, which I created in about 10 minutes work fine. The only thing you might want adjusted is that it can end at 5pm the following day if you start at 5 pm the current day. I don't know about that but test it out. If you don't know how to set up this as a macro let me know.... Sub CalcIt() Dim InTime As Double Dim AddTo As Double Dim EndMoment As Double Dim Priority As Integer Dim TheRow As Double Let TheRow = ActiveCell.Row Let InTime = Cells(TheRow, 5) 'E38 Let Priority = Cells(TheRow, 7) 'G38 If Hour(InTime) 17 Then Let InTime = Int(InTime) + (17 / 24) 'sets to 5pm. End If If Priority = 1 Then Let AddTo = 0.083333 ElseIf Priority = 2 Then Let AddTo = 0.166666667 ElseIf Priority = 3 Then Let AddTo = 1 ElseIf Priority = 4 Then Let AddTo = 2 ElseIf Priority = 5 Then Let AddTo = 7 End If Let EndMoment = InTime + AddTo If Hour(EndMoment) 17 Then 'if end time is after 5 pm then must go to next day 'Let AddTo = Hour(EndMoment) - 17 Let EndMoment = Int(EndMoment) Let EndMoment = EndMoment + 1 + (9 / 24) + AddTo 'move to 9 am next day End If If Weekday(EndMoment) = 6 Then Let EndMoment = EndMoment + 2 ElseIf Weekday(EndMoment) = 7 Then Let EndMoment = EndMoment + 1 End If Cells(TheRow, 8).Value = EndMoment End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|