I am in complicated situation in which i have to check multiple criteria and if all those are met then need to compare if a file is sent on Schedule Date and Time and then result should be either "Delayed" or "On time"

In the first step the formula should meet the following criterias

column A2 should be = to Col G2:G11

Column B2 should be = to Col H2:H11

Column C2 should be = to Col I2:I11

If formula meet all these 3 criteria (i.e. a particular file with a particular date and format finds on the ranges mentioned) then we need to compare if a file was sent on schedule date and on schedule time?

e.g. If a file Date 25-Oct-22 was sent on 26-Oct-22 it means answer should be delayed, similarly if a file Date 25-Oct-22 was sent on 25-Oct-22 where the schedule time was 11:00 AM but it was sent on 11:05 AM then the result should be again Delayed, this mean we need to compare that the file sent should be the on same day and before the final scheduled time. this can be compared with following

Currently I have only 1 formula which can check date and time which is mentioned below

=IF(AND(G2<=A2, J2<=D2), "On Time", "Delayed")

CodePudding user response：

You can use a formula like this,

• Formula used in cell E2

=IF(ISNUMBER(MATCH(1,(A2=$G$2:$G$11)*(B2=$H$2:$H$11)*(C2=$I$2:$I$11)*(D2>=$J$2:$J$11),0)),"On Time","Delayed")