How to create a datetime vector from text input and sum only certain values?

I have an Excel file from which I use two columns. Column A contains yearly (01.01.-31.12.) time values in text in 15min intervals (01.01. 00:00:00, 01.01. 00:15:00, ...) and column M contains corresponding numeric values for those times.

Is it possible to change column A into a datetime vector where either 4 rows combine into an hour or 95 rows combine to a day (the same happening to values in M)? I want to be able to calculate the daily/weekly/monthly sum/mean of values in column M.

This is my current plot where x-axis is just the text values from the excel file.

% Time on horizontal X-axis and power [kW] on Y-axis
tickStep = 2000;
[~,xAxis] = xlsread('PV_Ertraege.xlsx','32000 qm','A7:A35046');
yAxis = xlsread('PV_Ertraege.xlsx','32000 qm','M7:M35046') ./1000;
plot(yAxis)
set(gca,'xtick',1:tickStep:numel(xAxis))
set(gca,'xticklabel',xAxis(1:tickStep:numel(xAxis)))
xlim([0 numel(xAxis)+1])
xtickangle(90)
ylabel('Total output [kW]')

From this I have only been able to calculate the yearly sum of M7:M35046, but not any anything else.

I have been trying some solutions that I've found online, but to no luck.

% Tried and failed means of changing to datetime:

X = xlsread('PV_Ertraege.xlsx','32000 qm','A7:A35046') ;
t = datetime(X,'ConvertFrom','excel')

% and
A = xlsread('PV_Ertraege.xlsx','32000 qm','A7:A35046')
dv = datevec(A(:,1)) ;
[days,~,subs] =unique(dv(:,1)) ;
dailysum = accumarray(subs, A(:,1)) ;

Thank you to everyone who might be able to try and help!

Answer 1

[Peter Perkins]

First, use readtable, not xlsread. If you have a very recent version, use readtimetable. I'm not clear on exactly what's in your spreadsheet, so I can't say exactly how to do that latter.

"M contains corresponding numeric values for those times." If you mean excel serial date numbders, then readtable and readtimetable should automatically make datetimes in MATLAB for you.

"Column A contains yearly (01.01.-31.12.) time values in text in 15min intervals (01.01. 00:00:00, 01.01. 00:15:00, ...)" If you read these as text, convert to datetime:

>> datetime(["01.01. 00:00:00" "01.01. 00:15:00"],'InputFormat','MM.dd. HH:mm:ss')
ans = 
  1×2 datetime array
   01-Jan-2019 00:00:00   01-Jan-2019 00:15:00

Then make a timetable and use retime. daily, monthly, etc. sums etc. are all a one-liner.

[Tarmo Tukiainen]

Thank you for your help Peter!

I ended up finding a way to just create new datetime vectors and ignored the excel input for x-axis altogether with the following code. Posting these here in case someone runs into a similar problem.

if true
    t1 = datetime(2018,1,1,0,0,0) ;
    t2 = datetime(2018,12,31,23,45,0) ;
end
 timeMi = t1:minutes(15):t2 ; % Original size datetime (15min interval) (1x35040)
 timeH = t1:minutes(60):t2 ; % 1-hour interval datetime (1x8760)

And combined the values in my M-column from a 35040x1 double (PVe) to create a 8760x1 double (sub1), to then be used as y-axis against timeH;

 x = PVe; % Data 
size1 = length(x)/4; % / how many values you want to combine
sub1 = ones(size1,1); % intermediate matrix for averaging every hour
for n = 1:size1 % 35040/4 = 8760
    sub1(n) = sum(x(4*(n-1)+1 : (4*n))); % Sums every 4 values to sum every hour
end