Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.