How do I Perform Summation based on dates from Excel File

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 .

[Ted Shultz]

What do you need help with:

-getting the file into matlab

-doing the math in matlab

or just doing the math in excel

Answer 1

[Cris LaPierre]

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")

Answer 2

[Oghenovo Okpako]

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