NOTHING WORKS2-HELP

[Guest]

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.083333333333333333333333333333333","0.16666666666666666666666666666667","1","2","7"})

How can i adjust it to take into account working days (mon-fri 9am-5pm)

Thanks.

 

[Guest]

Try this:

=LOOKUP(I38,{1,2,3,4,5},E38+{0.0833333333333333,0.166666666666666,1,2,7})+(IF(WEEKDAY(E38)=6,2,IF(WEEKDAY(E38)=7,1,0)))

 

[Guest]

Thanks for the reply, but the formula can still compute answers which are out
of hours.

 

[Guest]

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

 

[Guest]

Yes, sorry for the confusion. I need it to take into account that people
don't work past 5pm daily.

Hope you can help.

 

[Guest]

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

 

[Guest]

Thanks for the time spent on that. I am a novice to macros though so any
directions would be great if possible.

Regards