Add missing time data

Hello, i have in excel 2 columns, A for time data DD/MM/YYYY HH:MM:SS and the other column are values from a variable X1.

and i want to create all the missing data time with a frequency of 1 min, and the X1 value being a blank cell or N/A something like that.

I have tried with a excel macro vba, but the excel always crashes so i wanted to know if it is possible to do that in matlab

Answer 1

[Peter Perkins]

Your data run from 1-Jan-2016 to 31-Dec-2016. There are

>> minutes(diff(datetime(2016,[1 12],[1 31])))
ans =
      525600

minutes in 2016. Do you really want that many rows? You can do it, but let's assume you really meant every 30 minutes. Using timetables, introduced in R2016b,

>> t = table2timetable(readtable('U1200 - Massa Vol£mica (1).xlsx'));
>> head(t)
ans =
  8×1 timetable
            Date              X1  
    ____________________    ______
    01-Jan-2016 00:00:00         0
    03-Jan-2016 05:00:00    0.7353
    04-Jan-2016 04:30:00    0.7338
    08-Jan-2016 04:30:00    0.7404
    11-Jan-2016 04:30:00    0.7393
    13-Jan-2016 11:30:00     0.736
    15-Jan-2016 04:30:00    0.7356
    18-Jan-2016 04:30:00    0.7398
>> regularTimes = datetime(2016,1,1,'Format','dd-MMM-yyyy HH:mm:ss'):minutes(30):datetime(2016,12,13,23,30,0);
>> t2 = retime(t,regularTimes);
>> head(t2)
ans =
  8×1 timetable
            Date            X1 
    ____________________    ___
    01-Jan-2016 00:00:00      0
    01-Jan-2016 00:30:00    NaN
    01-Jan-2016 01:00:00    NaN
    01-Jan-2016 01:30:00    NaN
    01-Jan-2016 02:00:00    NaN
    01-Jan-2016 02:30:00    NaN
    01-Jan-2016 03:00:00    NaN
    01-Jan-2016 03:30:00    NaN

[Tiago Dias]

Thanks for your input, and if in my excel sheet i have both 2016 and 2017 data? how would it be?

Question 2) datetime(2016,12,13,23,30,0) What means the 12, 13 , 23 , 30 and 0?

Sorry for all the questions, and once again thank you

[Walter Roberson]

datetime(2016,12,13,23,30,0)

That is year 2016, month 12, day 13, hour 23, minute 30, seconds 0