# Excel - IF Function Problem - Expert Solution

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.

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.

## Click here to get your free Excelchat help session

Trusted by people who work at