How do I Perform Summation based on dates from Excel File
1 view (last 30 days)
Show older comments
Oghenovo Okpako
on 11 Sep 2019
Answered: 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
Ted Shultz
on 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
Accepted Answer
Cris LaPierre
on 11 Sep 2019
Edited: Cris LaPierre
on 11 Sep 2019
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
More Answers (1)
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!