Priority

[MARKEGANDERSON]

Hello Guys.

I have a table as follows

Priority Required Time
1 10 mins
2 2 hours
3 48 Hours


Then on a separate Tab, cell R2 = I am checking the value of cell K2 (what Priority 1,2,or 3), once determine, then checks if the value in cell Q2 is > the "Required time" for that Priority, if it is Greater then "N", if not then "Y"

Priority is one column and required time is another.

 

[Yongle]

How are the values held in column Q?

1. With hours as decimal values in Q2 (example 24.3333 = 24 hours 20 minutes)

=IF(Q2>CHOOSE(K2,10/60,2,48),"N","Y")

Required time returned ..
If K2 =1 then 10/60
If K2 =2 then 2
If K2 =3 then 48

Checks if the value in cell Q2 is > the "Required time" for that Priority, if it is Greater then "N", if not then "Y"

So IF Q2 > the "Required time" then "N", if not then "Y"


2. IF column Q contain Excel TIME values, use this formula instead (example 24:20:00 = 24 hours 20 minutes)

=IF(Q2>CHOOSE(K2,10/60,2/24,48/24),"N","Y")

 

[MARKEGANDERSON]

Thanks Yongle.

I have another Hiccup. In regards to priority 3, which is 48 Hours. The catch is that there are only 8 hours in a day on the project. So if the vehicle was turned in on Day 1 and was completed on Day 3, that would be 24 hours not 48 hours.

How would I fix this hiccup? (I am subtracting Excel TIME values)

 

[Yongle]

I will look at this tomorrow ..


Are you saying that to satisfy 48 hours you need 6 days?
Does this give you the correct result?

=IF(Q2>CHOOSE(K2,10/60,2/24,6),"N","Y")

 

[MARKEGANDERSON]

Thanks again Yongle.

I have edited your formula: =IF(Q2>CHOOSE(K2,.1667,2,48),"N","Y"), and also formatted Q2 =(Start_Date - Date_in_Shop)*24 to Decimals, seems to be working just fine.

If you can help me figure out how to CAP the 24 hour period just to 8 hours vs 24 hours(Maybe a special formula in Q2)?

=IF(Q2>CHOOSE(K2,10/60,2/24,6),"N","Y") for this one the 10/60 does not seems to work. The "6" works as far as the criteria but the Q2 shows total of 146

So what I am trying to sav is if we can get Start_Date(8/14/2018 8:00:00 AM) - Date_in_Shop(8/15/2018 08:00:00 AM) = 8 hours vs 24 hours

Hope I did not make it anymore confusing

 

[Yongle]

Use this method
D2 is the earlier date, E2 is the later date

You want
(no of days X 8) + (no of hours)

The formula is:
=(INT(E2-D2)*8)+((E2-D2-INT(E2-D2))*24)