List of time data using start and end times and presenting duration as an hourly table


New Member
Jun 5, 2019
Hi all,

I have a list of activities that include the start and end time - I need to sum the duration of these activities into a table with hourly intervals. It would be easy enough if all the activity was only an hour in duration :LOL:, however there are activities that are an hour and 30 minutes long, 2 hours long etc...

So, I have my data in one tab, with a number of columns that contain, date, start time, end time, duration (formula end - start), activity name etc. In another tab I have a summary of the total duration broken down into hourly slots.

What I am looking to do is populate the tab with an hourly table which displays effort (duration of all events) each day to sum up the duration in each of the hour slots. For example, if I have an activity that starts at 09:00 and ends at 10:30, so a duration of 1.5 hours... I would like to display 1 hour in my hourly table next to 09:00-10:00 and 0.5 hours next to 10:00-11:00, also on the date that the activity occurs- I have a table matrix with hours from 08:00 to 20:00 listed on the left with the date vertically along the top. to elaborate, let's say i have another activity which starts at 09:30 and ends at 10:30, adding to the above, that is 0.5 hours in 09:00-10:00 and 0.5 in 10:00-11:00. If we took these to items together, my table should display 2 hours in 09:00-10:00 and 1 hour in 10:00-11:00 :eek:

Thank you in advance for any advice.. I don't seem to be able to post attachments, else I could provide an example...

PS: I don't want to do this as a pivot table, it needs to be a template format I can distribute.. I need it to be "fool proof" so I don;t have to rely on people being able to refresh a pivot table.


Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...