MATLAB Answers

0

How do I Perform Summation based on dates from Excel File

Asked by Oghenovo Okpako on 11 Sep 2019
Latest activity Answered by Oghenovo Okpako on 2 Oct 2019
Dear Sir/Madam,
I have a large data set in an excel file which has a column for date & time and a column for cost as given below. This data is collected for every ten minute interval begining from the date of 09/01/2013 at 22:50 to 22/05/2014 at 01:00:00. I am interested in calculating the cost for each and every date (i.e. 09/01/2013 will be the adding of the cost in column B within 22:50 and 23:50) and so on. Thereby creating another matrix/vector solely for the total cost for a given day. I have tried using the xlsxread('filename.xlsx') but the column A is displayed as NaN. Kindly help on this please. Best Regards
Column A (Date & Time) Column B (Cost)
09/01/2013 22:50 15
09/01/2013 23:00 4
09/01/2013 23:10 4
09/01/2013 23:20 3
09/01/2013 23:30 2
09/01/2013 23:40 7
09/01/2013 23:50 2
10/01/2013 3
10/01/2013 00:10 4
10/01/2013 00:20 18
10/01/2013 00:30 6
10/01/2013 00:40 7
10/01/2013 00:50 7
. .
. .
. .
. .
22/05/2014 01:00 .

  1 Comment

What do you need help with:
-getting the file into matlab
-doing the math in matlab
or just doing the math in excel

Sign in to comment.

2 Answers

Answer by Cris LaPierre
on 11 Sep 2019
Edited by Cris LaPierre
on 11 Sep 2019
 Accepted Answer

It sounds like you need to read the data in as a timetable.
opts = detectImportOptions('Oghenovo_Okpako_TT.xlsx');
data = readtimetable('Oghenovo_Okpako_TT.xlsx',opts)
Once the data is loaded, you can then use the retime function to work with the data by time/date. For example, this code will return the sum cost by day.
Cost_daily = retime(data,"daily","sum")

  0 Comments

Sign in to comment.


Answer by Oghenovo Okpako on 2 Oct 2019

@ Cris thanks for your support, have sorted it out.

  0 Comments

Sign in to comment.