converting a huge dataset from minutes to days

I have a huge dataset that goes from row A2 to row A105393. Each row has a timestamp of 5 minutes. For example row A2 contains the timestamp of 2020-01-01 00:00:38 while row A3 shows 2020-01-01 00:05:38. This goes on until the the end of 2020.

What I want is to summarize all those minutes into daily outputs. To get the daily demand for 2020-01-01, I have to manually drag and use the SUM function from A2 to A289. Having a dataset containing 105393 rows, this is gonna take some while to get the daily demand over a year. I have attached a small portion of the dataset.


Let's say I want the total demand for that whole day, how can I do this instead of dragging all the rows?

Thank you in advance!

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

Use a pivot table with the date/times as a row item and sum of outputs as a data field.

Excel will automatically group by days, months, years etc.

Thank you for your reply.

I have tried to use a pivot table, and tried to sort the date/time using Group Selection. I clicked on the first cell and then clicked on Analyze - Group Selection.
But I get an error saying that I can't group the marked area.

Is it because of the way the date/time is formulated in the cells?


EDIT:
I have tried to remove the time and only keeping the date for a small sample, and it seems I can group it this time.
So to make this work I have to remove the time for all the 105k+ rows... :P
Is there any faster way to do this? haha

Maybe:

E2
=SEQUENCE(366,,DATE(2020,1,1),1)

F2, copied down:
=SUMPRODUCT((LEFT(TRIM($A$2:$A$21),10)+0=E2)*$B$2:$B$21)

see sheet. Adjust ranges as needed in the SUMPRODUCT formula.

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.