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.
