MATLAB Answers

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

2 views (last 30 days)
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;
xlim([0 numel(xAxis)+1])
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!


Sign in to comment.

Accepted Answer

Peter Perkins
Peter Perkins on 30 Oct 2019
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.

  1 Comment

Tarmo Tukiainen
Tarmo Tukiainen on 4 Nov 2019
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) ;
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

Sign in to comment.

More Answers (0)

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by