0

I have two excel files with dates in each of them. The goal is to find the location of datetimes in file A in file B.

e.g. Excel file A has dates and each hour in column A from 1Jan1970 1AM to 31Dec2015 1AM with a lot of random missing dates and hour.

Excel file B has date e.g. 1jan1978 5PM

I read file A in array called A and do the following:

ind = find( x2mdate(A) == x2mdate(28491.7083333333) ); %datestr(x2mdate(28491.7083333333)) ans = 01-Jan-1978 17:00:00

it returns empty even though I can see that 1/1/1978 all hours are available in file A.

This is clearly a rounding issue. So, how do I deal with this? I tried using datestr but it is very slow.

Adriaan
  • 17,741
  • 7
  • 42
  • 75
Zanam
  • 4,607
  • 13
  • 67
  • 143

2 Answers2

2

Instead of x2mdate(28491.7083333333), try using:

datenum('01-Jan-1978 17:00:00', 'dd-mmm-yyyy HH:MM:SS')

It's easy to see that because of the rounding, they are not considered equal:

>> datenum('01-Jan-1978 17:00:00', 'dd-mmm-yyyy HH:MM:SS') == x2mdate(28491.7083333333)
ans =
     0
nirvana-msu
  • 3,877
  • 2
  • 19
  • 28
1

You are comparing to the wrong value. 28491.7083333333 is slightly off the value you are looking for. When you want to use a precise match with constant floats, you have to use 17 digits. Otherwise compare with a reasonable tolerance.

tol=datenum(0,0,0,0,0,60) %60 seconds tolerance
ind = find( abs(x2mdate(A) - x2mdate(28491.7083333333)<tol );
Community
  • 1
  • 1
Daniel
  • 36,610
  • 3
  • 36
  • 69