I have a case were I have an excel (.xlsx) file that has data associated with dates. The dates are not in any order and some are in the past. I only need the data from today and in the future and then sort the data/dates from closest to today, to furthest into the future. My current idea to approach this issue is to compare Today's date to each date in the excel and make a new array that will consist of "1" or "0" to denote if the date is in the past or not. Since the date from the excel sheet comes into matlab as a 1×1 cell, I am using extractBetween and str2double to turn the day, month and year into numbers I can put into datetime. The issue is that the date in the excel sheet can come in x/x/xxxx, xx/x/xxxx, or xx/xx/xxxx and I can only set up the extractBetween to set values. The first few dates are: 7/25/2011, 8/26/2016, 7/11/2016, 12/1/2016, 12/1/2016, 2/7/2017, 3/14/2017, 2/22/2017, 3/2/2017, 3/16/2017. The code runs correctly until it reaches 12/1/2016. How do I fix this/is there a better way to do this?
Code:
[num, txt, raw] = xlsread('example.xlsx');tod = date;a = size(raw);numrows = a(1,1);datecol = 4;currentrow = 3;i = 1;ad = [1;1]; while i == 1 d = raw(currentrow,datecol); mon = extractBetween(d,1,2); da = extractBetween(d,3,4); ye = extractBetween(d,6,9); newmon = str2double(mon); newda = str2double(da); newye = str2double(ye); if newmon > 0 t = datetime(newye, newmon, newda); else mon = extractBetween(d,1,1); newmon = str2double(mon); t = datetime(newye, newmon, newda); end if newda > 0 t = datetime(newye, newmon, newda); else da = extractBetween(d,3,3); newda = str2double(da); t = datetime(newye, newmon, newda); end if tod > t ad = [ad; 0]; currentrow = currentrow + 1; if numrows >= currentrow i = 1; else i = 0; end else ad = [ad; 1]; currentrow = currentrow + 1; if numrows >= currentrow i = 1; else i = 0; end endend
Best Answer