**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.*