sorting and calculating unique items

[mojobaabby]

I am not sure if this question is too complex for a sum if or if it should be a macro or vba?
I have a list of customers and shop floor actions that i want to sort by a date i choose and by employee. I want to calculate the total time an employee spent on each project on the day that i have chosen. They may spend time on the same project 6 different times in a day. I would like that unique customer consolidated to show each unique only once but add all the times for each unique that day, round to 15 minutes and show hh:mm. I have attached a sample data table as well as the report sheet layout. Yellow is the info I enter, blue is the result I need.
Any help would be appreciated.

[AlphaFrog]

Seems like this could be done with a Pivot Table (see attached). If you do a search for something like Excel Pivot table tutorials, you'll fine many useful guides/videos/tutorials on how to make them.

[mojobaabby]

Thanks that actually works great but when I add data (sept 15 for example) no matter what i try I cant get it to update.
I have tried renaming the range and refreshing but to no avail. Thanks

[AlphaFrog]

I understand what you're saying, but I have an older version of Excel (2003) and can't manipulate your version of the pivot table. I can see it though. Maybe if you were to save the example file as .xls, it might be backward compatible, but I'm not sure.

I recreated the Pivot Table with the original data and then added your new Sept 15 data. I simply refreshed the Pivot Table and it does show up for me. I Refreshed by right-clicking on the Pivot Table and selected Refresh Data from the pop-up context menu.

As a wild guess, try this;

• Right-click on the START DATE pivot.
• Select Field Settings\Advanced\Autosort = Asscending
• Then refresh again.

If it was on Manual sort, it may not have shown the new date column.

Another guess is maybe there is some sort of incompatibility issue for you when running my older version of the Pivot Table. You may want to build your own table and see if that helps.

On separate note, if you just wanted to display a specific week; have another formula column on the data sheet to calculate the week-start-date for each row and add that column as a Page Field (see attached.)

[mojobaabby]

ok so i figured out how to change the table location so it updates but i can no longer transfer my data. my macro says to transfer anything that the sheet is not named data so im getting a conflict. Thinking a pivot now may not be the answer?