How do I Perform Summation based on dates from Excel File

1 vue (au cours des 30 derniers jours)
Oghenovo Okpako
Oghenovo Okpako le 11 Sep 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 commentaire
Ted Shultz
Ted Shultz le 11 Sep 2019
What do you need help with:
-getting the file into matlab
-doing the math in matlab
or just doing the math in excel

Connectez-vous pour commenter.

Réponse acceptée

Cris LaPierre
Cris LaPierre le 11 Sep 2019
Modifié(e) : Cris LaPierre le 11 Sep 2019
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")

Plus de réponses (1)

Oghenovo Okpako
Oghenovo Okpako le 2 Oct 2019
@ Cris thanks for your support, have sorted it out.

Community Treasure Hunt

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

Start Hunting!

Translated by