Question description:
This user has given permission to use the problem statement for this
blog.
i need a formula to check if a certain time from one column is found in another column and i want it to return something
Solved by C. Y. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
10/02/2018 - 02:40
Hello , welcome to Got It Pro
Excelchat Expert
10/02/2018 - 02:41
Hello, are you there?
Excelchat Expert
10/02/2018 - 02:44
Hello
User
10/02/2018 - 02:44
hi
Excelchat Expert
10/02/2018 - 02:44
Good
Excelchat Expert
10/02/2018 - 02:44
Do you have a specific example ?
User
10/02/2018 - 02:44
yes i do
User
10/02/2018 - 02:44
but the file is too big
User
10/02/2018 - 02:45
however i will explain as best as i can
Excelchat Expert
10/02/2018 - 02:45
How big?
User
10/02/2018 - 02:45
over 20mb
User
10/02/2018 - 02:46
ARRIVAL_TIME Compliance/non compliance month ATTENDANCE_DATE Employee Names Activity of teachers Active /Non Active ACADEMY_DIM_ID ARRIVAL_REASON_DIM_ID DEPARTURE_TIME DEPARTURE_REASON_DIM_ID MISSING_RECORD_REASON_DIM_ID EXPECTED_ARRIVAL_TIME EXPECTED_DEPARTURE_TIME
17:57:00 August 14/08/2016 Morris N. Parwon Language Routine Inactive 25 NULL NULL NULL NULL 07:30:00 15:30:00
07:00:00 August 15/08/2016 Solomon G. Higgins #N/A Active 1 NULL 15:32:00 NULL NULL 07:30:00 15:30:00
06:59:00 August 15/08/2016 Samuel S. K. Gbellekoya Morning Prep Inactive 3 NULL 18:27:00 NULL NULL 07:30:00 15:30:00
07:07:00 August 15/08/2016 Joseph S. Karlon Attendance Inactive 5 NULL 15:31:00 NULL NULL 07:30:00 15:30:00
08:24:00 August 15/08/2016 Aliu V. Massaquoi FWOS Lesson 6 Active 11 NULL 16:01:00 NULL NULL 07:30:00 15:30:00
19:26:00 August 16/08/2016 Albert M. Dolo GOC Active 2 NULL NULL NULL NULL 07:30:00 15:30:00
08:02:00 August 16/08/2016 Samuel S. K. Gbellekoya Morning Prep Inactive 3 NULL 16:57:00 NULL NULL 07:30:00 15:30:00
07:00:00 August 16/08/2016 Joseph S. Karlon Attendance Inactive 5 NULL 15:50:00 NULL NULL 07:30:00 15:30:00
07:56:00 August 16/08/2016 Aliu V. Massaquoi FWOS Lesson 6 Active 11 NULL 16:01:00 NULL NULL 07:30:00 15:30:00
07:03:00 August 17/08/2016 Joseph S. Karlon Attendance Inactive 5 NULL 15:30:00 NULL NULL 07:30:00 15:30:00
07:31:00 August 17/08/2016 Aliu V. Massaquoi FWOS Lesson 6 Active 11 NULL 17:01:00 NULL NULL 07:30:00 15:30:00
Excelchat Expert
10/02/2018 - 02:46
Thats big, you can put some sample data on the Google Sheet here or in another excel file, and I will put some formulas for you which you can replicate
Excelchat Expert
10/02/2018 - 02:47
Can you copy that from your Excel sheet and paste it into Sheet 2 here?
User
10/02/2018 - 02:47
i done that
Excelchat Expert
10/02/2018 - 02:47
You pasted into the chat window. Click on a cell in Sheet 2 and paste
User
10/02/2018 - 02:48
am trying to see if the arrival time is equal to 07:30 to return compliance otherwise non compliance
User
10/02/2018 - 02:48
yes i have done that sir
Excelchat Expert
10/02/2018 - 02:49
Sorry, I see your data.
User
10/02/2018 - 02:49
i tried if statement but was returning wrong data
Excelchat Expert
10/02/2018 - 02:50
ok so if arrival time is exactly 07:30 or do you mean greater than or equal or do you mean less than or equal?
User
10/02/2018 - 02:50
less than or equal to
Excelchat Expert
10/02/2018 - 02:51
OK, so if time is less than or equal then it is non-compliant?
User
10/02/2018 - 02:51
Also i have the expected time of arrival in column m
User
10/02/2018 - 02:51
yes sir
User
10/02/2018 - 02:51
no it's compliance
Excelchat Expert
10/02/2018 - 02:52
OK, so in your data the 1st row is not compliant and the 2nd data is compliant?
User
10/02/2018 - 02:52
correct sir
Excelchat Expert
10/02/2018 - 02:52
Please wait whilst I put in the formula
Excelchat Expert
10/02/2018 - 02:53
Extend the chat if you see the option
User
10/02/2018 - 02:53
will do
Excelchat Expert
10/02/2018 - 02:54
I have put the formula
User
10/02/2018 - 02:54
so what formula did you use sir
Excelchat Expert
10/02/2018 - 02:55
=if(A2<=time(7,30,0),"Compliant","Non-Compliant")
User
10/02/2018 - 02:55
i tried this several time but didn't work for me
Excelchat Expert
10/02/2018 - 02:55
the function TIME(7,30,0) is the key
User
10/02/2018 - 02:56
i used that initially
Excelchat Expert
10/02/2018 - 02:56
You want to compare your time against a time also
Excelchat Expert
10/02/2018 - 02:56
Maybe there was a mismatch between your data and the TIME object
User
10/02/2018 - 02:56
i got another question sir
Excelchat Expert
10/02/2018 - 02:56
OK, Go ahead
User
10/02/2018 - 02:57
if i want to find out how mant times a name appears in a month how do i go about that sir
User
10/02/2018 - 02:57
or an id
Excelchat Expert
10/02/2018 - 02:57
We can use the COUNTIF function
Excelchat Expert
10/02/2018 - 02:58
Let me show an example
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.
