If function checking 2 time ranges

thanks for your reply.

Your reply will definitely help me to create required logic which i have started,

These 4 cases will be in added in single cell depending on what time vehicle arrives then check unloading time and show actual result, for example

a vehicle arrives at 22:00 hours then cell should check report time belongs to case no 2 which has unloading benchmark 11:59 Hours, cell will check two times A1 (reporting time i-e 22:00), A2 (unloading time i-e 13:00) then cell should show B as time exceeds benchmark.

you may want to just check the starting time - and then use a nested if

=IF(AND(HOUR(A2)>=12,HOUR(A2)<17) , IF( HOUR(B2)<=4),"PASS","Calculate the benchmark unloading time") , NOW repeat for the rest of the times arriving - etc IF(....

need your more support, could not get required results

Sample Sheet vehicle in n out.xlsx

I have attached sample sheet with different cases in which vehicles arrives and leaves factory.

we need to see the spreadsheet sample
also I have added a formula
=IF(AND(HOUR(A2)>=12,HOUR(A2)<17),IF(HOUR(B2)<=4,"PASS","Calculate the benchmark unloading time"),IF(OR(AND(HOUR(A2)>=17,HOUR(A2)<23),AND(HOUR(A2)>=0,HOUR(A2)<3)),IF(HOUR(B2)<=11,"PASS","Calculate the benchmark unloading time"),IF(AND(HOUR(A2)>=3,HOUR(A2)<8),IF(HOUR(B2)<=16,"PASS","Calculate the benchmark unloading time"),IF(AND(HOUR(A2)>=8,HOUR(A2)<12),IF(HOUR(B2)<=11,"PASS","Calculate the benchmark unloading time"),"time not in tests"))))

which will deal with the cross midnight time scale

also the loading time needs to be revised
Case 1 if vehicle arrives at factory between 12:00 hours to 16:59 hours and unloaded before 05:00 then show "A" if after 05:00 then "B"
Case 2 if vehicle arrives at factory between 17:00 hours to 02:59 hours and unloaded before 12:00 then show "A" if after 12:00 then "B"
Case 3 if vehicle arrives at factory between 03:00 hours to 07:59 hours and unloaded before 17:00 then show "A" if after 17:00 then "B"
Case 4 if vehicle arrives at factory between 08:00 hours to 11:59 hours and unloaded before 11:59 then show "A" if after 11:59 then "B"

SO for
case 1 unloaded after 12:00 and before 05:00 - crossing midnight - so calc needs to be modified to deal with midnight (as modified in case 2 arrival time)
case 2 unloaded after 17:00 and before 12:00 - crossing midnight - so calc needs to be modified to deal with midnight (as modified in case 2 arrival time)
case 3 unloaded after 03:00 and before 17:00
case 4 unloaded after 08:00 and before 11:59 - although if a lorry can arrive upto 11:59 - then how can it be unloaded before 11:59

can you confirm the modification to business rules for case 1,2 & 3 and also confirm the rules for case 4

There are 4 time ranges (business Rules)already mentioned below as cases 1 to 4 and each vehicle will check 2 data entries Vehicle Report Time (column D) and Vehicle out time (column G) in attached spreadsheet.

Vehicle report time will decide which business rule (case) to be followed corresponding to which benchmark time are set, vehicle out time will decide extra payment to be given to transporter or not.

I do not know if it is allowed to give skype ID, i can explain well on video conference.

Dear All, need your support to automate sheet.

I have checked different possible solutions and thought this can be achieved by adding data in two columns, one column to select case then other column to check vehicle left factory on benchmark or not (Extra payment)

I would appreciate if anyone could guide me if I am using correct formula for below case

case 2
vehicles reporting time is between 17:00 hours to next day 03.:00 hours

=IF(AND(DAY(C2),HOUR(D2)>=17,(DAY(C2)+1),HOUR(D2)<3),"CASE4")

i have attached sheet

see my post 8 and questions around the case

for report time - whats the rules for each case ?
for out time - whats the rules for that case

Case 1 if vehicle arrives at factory between 12:00 hours to 16:59 hours - REPORT TIME and unloaded after the REPORT TIME but before OUT TIME of 05:00 then show "A" if after 05:00 then "B"
Case 2 if vehicle arrives at factory between 17:00 hours to 02:59 hours - REPORT TIME and unloaded after the REPORT TIME but before OUT TIME of 12:00 then show "A" if after 12:00 then "B"
Case 3 if vehicle arrives at factory between 03:00 hours to 07:59 hours - REPORT TIME and unloaded after the REPORT TIME but before OUT TIME of 17:00 then show "A" if after 17:00 then "B"
Case 4 if vehicle arrives at factory between 08:00 hours to 11:59 hours - REPORT TIME and unloaded after the REPORT TIME but before OUT TIME of 11:59 then show "A" if after 11:59 then "B"

Case4 is not making sense based on in some cases it needs to unloaded when it arrives

thanks for your reply.

oh! sorry it is typo mistake.... unloading benchmark for case 4 is 23:59 hours. sorry for inconvenience

Excellent!!!!!!!!!

It is working fine...... just had issues in very few lines for which i have attached sheet to check, yellow highlighted lines should have shown Yes but appearing NO and rest showing error not in range.



THANK YOU BOSS!

is this the correct spreadsheet - looks like your original ?

Yes this is the original sheet, i have still removed so many columns just left relevant data

not sure then what is not working

I have expanded the tests so we have case1 - 4 and also the arrival time now tested as a fault finding exercise
which rows are not working ?

see the separate tests in row 10 headers - and testing is in row 11- 17 - testing the times in D2/G2 to D8/G8

Please check Row number 1,4,5,6 & 7 cell showing not in range while for row number 2,3,8 Cell should show Yes but NO is appearing.

not in range appearing for time exceeding 23:00 hours! result was showing correct in most of the cases.

not in range appearing for time exceeding 23:00 hours! result was showing correct in most of the cases.

should have had D4<24 not 23 in the formula
modified

and the outime - is the next day - as far as the rule is concerned - i will need to think about that

Excellent!!!
Checked all cases, all are showing correct results. I have got what I required.... Thanks a lot.

Soon i will will be adding another condition in file

Once again thanks for your help.

Excellent!!!
Checked all cases, all are showing correct results. I have got what I required.... Thanks a lot.

Soon i will will be adding another condition in file

Once again thanks for your help.

We can simply the IFs now to include the outtime as well as arrival time - i broke it out for diagnostic and testign purposes - that will reduce the IFs as there will not only be one per case and allow in excel 2003 the other conditions if required - but that will have to be later as out for next couple of days

Hi there......

adding one more condition in sheet

Column G Showing factory locations U or D and Column F showing Vehicle Type 20 feet or 40 feet now there is another condition if a 40 feet vehicle arrives in Factory D at or after 00:00 hours then benchmark for unloading is 17:00 hours.

This condition is only applicable in location D, i have also attached sheet for your reference.

we would need to simplify the IFs

but need more information on how to handle the new criteria

arrives in Factory D at or after 00:00 hours then benchmark for unloading is 17:00 hours

- so is that anytime between 00:00 and 17:00 - and out by 17:00 the same day (although I know arriving at 17:00 and unloading an out by 17:00 would not be practical)

what happens for other arrival times after 17:00 or practically would need to be earlier ? do we follow the other rules , as we may need to test for the size on all criteria - so ALL business rules would be useful for the NEW lorries

what happens in factory U - just follow existing rules

are you using excel 2003 - as that has a limit of 7 nested IFS and we may need to review the approach

This rule is only for Factory D with vehicle type 40.

if a Truck reaches to factory D and vehicle type is 40 and arrives between 00:00 hours to 08:00 Hours then benchmark for unloading truck is 17:00 hours.

There will be no change for factory U and rest all rules will remain same, I am using excel 2007.

we need to set up a set of conditions for Factory D and 40 - what happens when a truck arrives after 8:00 and before 00:00

Case 1 if vehicle arrives at factory between 12:00 hours to 16:59 hours and unloaded before 05:00 then show "A" if after 05:00 then "B"
Case 2 if vehicle arrives at factory between 17:00 hours to 02:59 hours and unloaded before 12:00 then show "A" if after 12:00 then "B"
Case 3 if vehicle arrives at factory between 03:00 hours to 07:59 hours and unloaded before 17:00 then show "A" if after 17:00 then "B"
Case 4 if vehicle arrives at factory between 08:00 hours to 11:59 hours and unloaded before 11:59 then show "A" if after 23:59 then "B"

as the other rules do not match
case 4 , case1 are OK
BUT then you need to modify the rules for case 2 between 17:00 -00:00

OR is it simply the truck fails if arrives after 8:00 and before 00:00

Its good to know you are on 2007 , as that can take a lot more nested IFs - so we can set up a test for factory D and truck 40 - and do all the tests to cover those conditions

there is correction in Case 4, i have updated benchmark for unloading.

Case 1 if vehicle arrives at factory between 12:00 hours to 16:59 hours and unloaded before 05:00 then show "A" if after 05:00 then "B"
Case 2 if vehicle arrives at factory between 17:00 hours to 02:59 hours and unloaded before 12:00 then show "A" if after 12:00 then "B"
Case 3 if vehicle arrives at factory between 03:00 hours to 07:59 hours and unloaded before 17:00 then show "A" if after 17:00 then "B"
Case 4 if vehicle arrives at factory between 08:00 hours to 11:59 hours and unloaded before 00:00 then show "A" if after 00:00 then "B" (corrected benchmark)
Case 5 if vehicle arrives at factory D and vehicle type is of 40 and arrival time is between 00:00 to 08:00 hours then unloading benchmark would be 17:00 hours.

i needed just the rules for
factory D and vehicle type is of 40

Case 5 if vehicle arrives at factory D and vehicle type is of 40 and arrival time is between 00:00 to 08:00 hours then unloading benchmark would be 17:00 hours.

What happens at the other times and those time do not work with the current case 1-4

Rules for factory D will remain same from Case 1 to case 4, only if vehicle type 40 arrives between 00:00 hour to 08:00 hours it benchmark for unloading will be 17:00 hours instead of 12:00 hours.

Basically Rule number 03 is modified for vehicle type 40 in Factory D (Actual arrival time 03:00 to 07:59 hours is modified as arrival time 00:00 to 07:59 hours while benchmark remains same i-e before 17:00 hours)
My skype ID is (farhanthaheem)

for some reason the forum wont allow me onto page 3 - but i read the reply on my email

Rules for factory D will remain same from Case 1 to case 4, only if vehicle type 40 arrives between 00:00 hour to 08:00 hours it benchmark for unloading will be 17:00 hours instead of 12:00 hours.

Basically Rule number 03 is modified for vehicle type 40 in Factory D (Actual arrival time 03:00 to 07:59 hours is modified as arrival time 00:00 to 07:59 hours while benchmark remains same i-e before 17:00 hours)
My skype ID is (farhanthaheem)

Just the D / 40 then - we need to modify RULE 2 to

Case 1 if vehicle arrives at factory between 12:00 hours to 16:59 hours and unloaded before 05:00 then show "A" if after 05:00 then "B"
Case 2 if vehicle arrives at factory between 17:00 hours to 23:59 hours and unloaded before 12:00 then show "A" if after 12:00 then "B"
Case 3 if vehicle arrives at factory between 00:00 hours to 07:59 hours and unloaded before 17:00 then show "A" if after 17:00 then "B"
Case 4 if vehicle arrives at factory between 08:00 hours to 11:59 hours and unloaded before 00:00 then show "A" if after 00:00 then "B" (corrected benchmark)

So those rules above now cover the 40 D conditions ?

No this will not work, existing formula will remain same there will be additional if condition to check this D / 40 conditions.

Existing rules 1 to 4 will remain same for factory U and Factory D (vehicle Type 20)

leaving all the current conditions as written for all the other vehicles except the 40/D lorry

SO JUST the 40/D lorry -

No this will not work, existing formula will remain same there will be additional if condition to check this D / 40 conditions.

but it cant follow the other rule case 2 - as that overlaps the arrival time - so case 2 and case 3 need to be modified for Lorry 40/D otherwise the arrival time overlaps

is that not the case - ?

correct..... this will resolve this issue 40/D condition..

not working

would appreciate if you update formula on attached sheet.

would appreciate if you update formula on attached sheet.

i did - see attached file on previous post

so what condition can we use dates and just change the factory from D to U to see it change Yes/No

Seems i made a mistake in copying formula...... It is working fine.... checking entries and give you feedback in an hour.... Thanks for your support

Ok - fingers crossed its all OK

Excellent!!!!!!

all working fine........ now there are no more conditions. all clear. thanks again

excellent :up: