Question description:
This user has given permission to use the problem statement for this
blog.
I received help from someone else on this website who provided me with the code; '' =if(and(B1-A1>=VALUE(ʺ-00:05:00ʺ),B1-A1<=VALUE(ʺ00:05:00ʺ)),ʺMATCHʺ,ʺNO MATCHʺ) '' This does not work for me? I've tried changing the cells but still unsuccessful . I'd like IF A1 and B1 match, come back Match, If A1 and B1 do not match, unmatch If A1 and B1 match with a 5 minute tolerance then match. Please can you assist, and advise if I need to change the cells (A1 B1) to (AB1 AB2) What the method is?
Solved by M. L. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
27/11/2017 - 08:40
Welcome
User
27/11/2017 - 08:41
Hi There
Excelchat Expert
27/11/2017 - 08:42
Can you please share the data sheet with me?
User
27/11/2017 - 08:43
I cannot, but there is sample data on side
User
27/11/2017 - 08:43
A1 B1 = Match,
User
27/11/2017 - 08:43
A2 B2, no match
User
27/11/2017 - 08:43
A3 B3 match.
User
27/11/2017 - 08:44
5 minute tolerance.
User
27/11/2017 - 08:44
I received help from someone else on this website who provided me with the code; '' =if(and(B1-A1>=VALUE(ʺ-00:05:00ʺ),B1-A1<=VALUE(ʺ00:05:00ʺ)),ʺMATCHʺ,ʺNO MATCHʺ) '' This does not work for me? I've tried changing the cells but still unsuccessful . I'd like IF A1 and B1 match, come back Match, If A1 and B1 do not match, unmatch If A1 and B1 match with a 5 minute tolerance then match. Please can you assist, and advise if I need to change the cells (A1 B1) to (AB1 AB2) What the method is?
Excelchat Expert
27/11/2017 - 08:45
Okay, let me work on this
User
27/11/2017 - 08:48
Alright thanks
Excelchat Expert
27/11/2017 - 08:56
please wait, I am working locally
User
27/11/2017 - 08:58
Take your time :)
User
27/11/2017 - 09:06
Just to advise I have 13:50 eft
User
27/11/2017 - 09:06
left
Excelchat Expert
27/11/2017 - 09:07
No worries
Excelchat Expert
27/11/2017 - 09:12
Done
Excelchat Expert
27/11/2017 - 09:12
Thanks for waiting
Excelchat Expert
27/11/2017 - 09:13
Let me share a file with you
Excelchat Expert
27/11/2017 - 09:14
File
[Uploaded an Excel file]
Excelchat Expert
27/11/2017 - 09:14
=IF(AND(ABS((B1-A1)*1440)>=-5,ABS((B1-A1)*1440)<=5),"Match","No Match")
Excelchat Expert
27/11/2017 - 09:14
Please use this formula
Excelchat Expert
27/11/2017 - 09:15
and also you need to change the date format as per your system format
User
27/11/2017 - 09:15
Does not work
User
27/11/2017 - 09:15
I cannot change the format on the data.
Excelchat Expert
27/11/2017 - 09:15
I used date format as mm/dd/yy
Excelchat Expert
27/11/2017 - 09:16
How is date displayed on your destop?
Excelchat Expert
27/11/2017 - 09:16
you system date format and data date format in excel should be same
Excelchat Expert
27/11/2017 - 09:16
otherwise formula calculation will not show the results
User
27/11/2017 - 09:19
how do I do that, is there no way around it..
Excelchat Expert
27/11/2017 - 09:19
You can see in preview window that formula is working as per your requirment
Excelchat Expert
27/11/2017 - 09:20
Can you please confirm how date is being shown on your desktop?
Excelchat Expert
27/11/2017 - 09:22
Date format on your PC desktop and in your Excel sheet should be same
User
27/11/2017 - 09:22
Okay
User
27/11/2017 - 09:22
its shown as : 19//2017 08:27:00
User
27/11/2017 - 09:23
19/01/2017 08:27:00
User
27/11/2017 - 09:23
for example
Excelchat Expert
27/11/2017 - 09:24
Please change the region settings on your system
User
27/11/2017 - 09:25
I am not able to, its for a business plus multiple people will be using this formula and it would be a hassle to change everyones system format
User
27/11/2017 - 09:25
is there no way to work around my format
User
27/11/2017 - 09:25
dd/mm/yyyy hh:mm
User
27/11/2017 - 09:25
what should it be
Excelchat Expert
27/11/2017 - 09:26
Then please change your date format on data sheet
User
27/11/2017 - 09:26
to what
Excelchat Expert
27/11/2017 - 09:26
it should be mm/dd/yyyy
Excelchat Expert
27/11/2017 - 09:27
because excel pickup the default format from region settings
User
27/11/2017 - 09:27
are we able to have miliseconds
Excelchat Expert
27/11/2017 - 09:27
Excel does not have that option
Excelchat Expert
27/11/2017 - 09:27
only hh:mm:ss
User
27/11/2017 - 09:27
I meant seconds
User
27/11/2017 - 09:28
sorry,
User
27/11/2017 - 09:28
It's not working
Excelchat Expert
27/11/2017 - 09:29
Because Formula is return the the value for minutes
Excelchat Expert
27/11/2017 - 09:29
This part of function is returning the value for minutes
Excelchat Expert
27/11/2017 - 09:29
ABS((B3-A3)*1440)
Excelchat Expert
27/11/2017 - 09:30
ABS((B1-A1)*1440)
User
27/11/2017 - 09:30
I see
User
27/11/2017 - 09:30
but the formula works in the file you sent me
User
27/11/2017 - 09:30
but not in my own data file
User
27/11/2017 - 09:30
Oh
User
27/11/2017 - 09:30
sorry, it does
User
27/11/2017 - 09:30
apologies
Excelchat Expert
27/11/2017 - 09:31
Thanks :)
User
27/11/2017 - 09:31
If I want to change the cell area
User
27/11/2017 - 09:32
for example instead of A1 B1 or was AK1 AL1
Excelchat Expert
27/11/2017 - 09:32
You can change the cell references
User
27/11/2017 - 09:33
it says #VALUE!
Excelchat Expert
27/11/2017 - 09:34
Final file
[Uploaded an Excel file]
Excelchat Expert
27/11/2017 - 09:34
Please check this out
Excelchat Expert
27/11/2017 - 09:35
and set the cells formatting as this file
User
27/11/2017 - 09:35
okay awesome
Excelchat Expert
27/11/2017 - 09:35
**as per this file
Excelchat Expert
27/11/2017 - 09:35
Are you satisfied with the results?
User
27/11/2017 - 09:35
that's now dd/mm right?
User
27/11/2017 - 09:35
Im happy yeah
Excelchat Expert
27/11/2017 - 09:37
its mm/dd/yy h:m
Excelchat Expert
27/11/2017 - 09:37
mm/dd/yy h:mm
User
27/11/2017 - 09:38
brilliant
User
27/11/2017 - 09:38
it works
Excelchat Expert
27/11/2017 - 09:39
And please keep the cells format as "General" where you are using formula
User
27/11/2017 - 09:39
okay
Excelchat Expert
27/11/2017 - 09:39
Session time is ending. Please rate 5 stars, if you liked the solution :)
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.
