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
, 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
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.
Thanks
Martin
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
, 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
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.
Thanks
Martin
