**Question description:**

*This user has given permission to use the problem statement for this blog.*

I NEED A FORMULA FOR : I HAVE A START TIME AND 4 ELEMENTS, 3 ELEMENTS ARE DATE AND TIME FIELDS, THE 4TH IS A YES OR NO. i NEED FORMULA TO TELL ME IF THE 4 FIELDS ARE DONE IN LESS THAN 3 HOURS

Solved by D. Y. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

User
13/09/2018 - 06:01

ARE YOU WORKING ON MY ISSUE?

Excelchat Expert
13/09/2018 - 06:01

Welcome, Thanks for choosing Got It Pro-Excel. I can help you with that problem.

Excelchat Expert
13/09/2018 - 06:01

Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.

Excelchat Expert
13/09/2018 - 06:01

Do you have sample data which we can use to illustrate how to solve this problem?

User
13/09/2018 - 06:02

YES

Excelchat Expert
13/09/2018 - 06:02

Kindly provide it.

User
13/09/2018 - 06:08

DONE

User
13/09/2018 - 06:09

DO YOU SEE IT?

Excelchat Expert
13/09/2018 - 06:14

Let me know once you are done... please

User
13/09/2018 - 06:14

I AM DONE

Excelchat Expert
13/09/2018 - 06:14

Okay.

Excelchat Expert
13/09/2018 - 06:14

Is it correct to say that the time taken in completing the activities mention is equal to:

Excelchat Expert
13/09/2018 - 06:14

= 3RD LAB DRAWN - ARRIVAL DATE AND TIME

Excelchat Expert
13/09/2018 - 06:14

?

User
13/09/2018 - 06:16

3RD LAB DRAWN, IS NOT THAT IT IS DRAWN 3RD, BUT THAT IS IS 3RD ORDERED. SO D CAN SOMETIMES HAVE A TIME BEFORE B

User
13/09/2018 - 06:16

B,C D ARE NOT ALWAYS CONSECUTIVE

Excelchat Expert
13/09/2018 - 06:17

Okay.

Excelchat Expert
13/09/2018 - 06:18

So basically we should get the difference in time between the arrival time and the latest lab drawn?

User
13/09/2018 - 06:19

YES, AND THEN ONLY IF E IS YES, THEN IT WOULD BE COMPLIANT

Excelchat Expert
13/09/2018 - 06:19

Okay.

Excelchat Expert
13/09/2018 - 06:20

Based on what you’ve shared, you need an IF function that will check both criteria and then return a Yes or No.

Excelchat Expert
13/09/2018 - 06:20

Do you believe that will address your problem?

Excelchat Expert
13/09/2018 - 06:21

Let me formulate the formulas, please give me a couple of minutes...will get back to you.

User
13/09/2018 - 06:21

NOT YES OR NO, I NEED COMPLAINT OR FALLOUT AS ANSWER

Excelchat Expert
13/09/2018 - 06:21

Okay.

Excelchat Expert
13/09/2018 - 06:21

Thank you for the clarification.

Excelchat Expert
13/09/2018 - 06:26

On the final phase of the formula..

Excelchat Expert
13/09/2018 - 06:39

It has been a long formula. I am polishing it....

Excelchat Expert
13/09/2018 - 06:39

Thank you for your patience.

User
13/09/2018 - 06:39

NO PROBLEM

Excelchat Expert
13/09/2018 - 06:42

What if the time is EXACTLY 3 hours

User
13/09/2018 - 06:42

COMPLIANT

Excelchat Expert
13/09/2018 - 06:42

Compliant or Fallout?

Excelchat Expert
13/09/2018 - 06:42

Okay.

Excelchat Expert
13/09/2018 - 06:45

Again, thank you for your patience.

Excelchat Expert
13/09/2018 - 06:45

The formula is:

Excelchat Expert
13/09/2018 - 06:45

=IF(AND(E2="Y",VALUE(LEFT(TEXT(MAX(B2:D2)-A2,"h:mm"),FIND(":",TEXT(MAX(B2:D2)-A2,"h:mm"))-1))*60+VALUE(RIGHT(TEXT(MAX(B2:D2)-A2,"h:mm"),LEN(TEXT(MAX(B2:D2)-A2,"h:mm"))-FIND(":",TEXT(MAX(B2:D2)-A2,"h:mm")))) <= 180),"Compliant", "Fallout")

Excelchat Expert
13/09/2018 - 06:46

Put it in F2

Excelchat Expert
13/09/2018 - 06:46

It returns "Compliant"

User
13/09/2018 - 06:47

I THINK THAT IS THE LONGEST FORMULA I HAVE EVER SEEN

Excelchat Expert
13/09/2018 - 06:47

Does this solution solve your problem?

Excelchat Expert
13/09/2018 - 06:47

Hahaha

User
13/09/2018 - 06:47

I AM TRYING IT NOW

Excelchat Expert
13/09/2018 - 06:47

There are longer formulas than that.

Excelchat Expert
13/09/2018 - 06:48

It is because of the nature of the type of data, that is why it is long.

User
13/09/2018 - 06:48

OK, WORKING ON IT NOW

Excelchat Expert
13/09/2018 - 06:53

Are you there?

User
13/09/2018 - 06:53

YES

Excelchat Expert
13/09/2018 - 06:54

Any problem or assistance you may need?

Excelchat Expert
13/09/2018 - 06:54

I can even explain to you how it works.

User
13/09/2018 - 06:55

IF ON MY ORIGINAL SPREADSHEET MY B AND C ARE ARE ACTUALLY B AND C, BUT D IS REALLY F, CAN YOU ADJUST

Excelchat Expert
13/09/2018 - 06:55

Okay.

Excelchat Expert
13/09/2018 - 06:57

Modified:

Excelchat Expert
13/09/2018 - 06:57

=IF(AND(E2="Y",VALUE(LEFT(TEXT(MAX(B2,C2,F2)-A2,"h:mm"),FIND(":",TEXT(MAX(B2,C2,F2)-A2,"h:mm"))-1))*60+VALUE(RIGHT(TEXT(MAX(B2,C2,F2)-A2,"h:mm"),LEN(TEXT(MAX(B2,C2,F2)-A2,"h:mm"))-FIND(":",TEXT(MAX(B2,C2,F2)-A2,"h:mm")))) <= 180),"Compliant", "Fallout")

Excelchat Expert
13/09/2018 - 06:58

We have less than 3 minutes.

Excelchat Expert
13/09/2018 - 06:58

Kindly let me know that it works fine.

Excelchat Expert
13/09/2018 - 07:00

Unfortunately our time is over.

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*