@kheldar The data you provided is full with #REF errors, probably referring to real employee names that you deleted (though they still show up when you try to filter the pivot table) and replaced by "Employee 1", "Employee 2" etc. None of these fake employees seem to have time records in the span from 8-16, so I changed some of the #REFs to "Employee 1". And to make the GETPIVOTDATA to work, I changed the timestamps in "hours1" to texts with formula:
=TEXT([@hours],"00")&":00"Then, I also changed the time labels in A4:A12 in the "Morning" table to plain text. This is the result:
The numbers, obviously make no sense because I just picked some time entries for each hour and assigned those to Employee 1. But the formula works.
Having done all that, I just wonder why you don't use one or more pivot tables to do the summarisations for you, without complicated formula. A simple PT (with a filter on the time stamp) could result in this:
Same result, just looking a bit different. Up to you.
Accepted Solutions
@kheldar The data you provided is full with #REF errors, probably referring to real employee names that you deleted (though they still show up when you try to filter the pivot table) and replaced by "Employee 1", "Employee 2" etc. None of these fake employees seem to have time records in the span from 8-16, so I changed some of the #REFs to "Employee 1". And to make the GETPIVOTDATA to work, I changed the timestamps in "hours1" to texts with formula:
=TEXT([@hours],"00")&":00"Then, I also changed the time labels in A4:A12 in the "Morning" table to plain text. This is the result:
The numbers, obviously make no sense because I just picked some time entries for each hour and assigned those to Employee 1. But the formula works.
Having done all that, I just wonder why you don't use one or more pivot tables to do the summarisations for you, without complicated formula. A simple PT (with a filter on the time stamp) could result in this:
Same result, just looking a bit different. Up to you.