Unable to populate column using data from two different sources

Haha i thought i was pretty good at excel but this one has me completely stumped. i might be overthinking it i don't know. I've stripped it right back though so i can get some second eyes over it.

Here's what it should do:
Pick a name from the dropdown on the cover tab and it display how much work was done each hour.

Here's the Problem
The formatting on the raw data tab is horrible. Time and Date are all in one cell and its a decimal number that's formatted instead of a text cell, because that would be to easy! (that is where most of my problems have come from) Now this data will be pulled daily so the decimal number is always different.

any help would be hugely appreciated i can't tell you how many hours i've been staring at this thing for !

I've attached a version with a lot less info in for you guys to have a look at the core data is there just all the extra is removed.

Thanks !

Unfortunately your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Please update your thread title as per the Moderator's request; I have a solution for you but I cannot post it until you have complied with the request.

deleted deleted as your post does not comply with Rule 1

e4=SUMPRODUCT((HOUR(Raw!$A$2:$A$530)=MOD(LOOKUP(9^9,LEFT($D4,{1,2})+0),12)+IF(RIGHT($D4,2)="pm",12,0))*(Raw!$B$2:$B$530=$C$3)*(Raw!$C$2:$C$530))
try this and copy towards down

Or:

Formula:

Please Login or Register  to view this content.

Sorry: should have said that I changed the times like 7am to 7 AM, that is, a proper time (with a numeric value) and then multiplied by 24 to make the comparison.

And another alternative without changing the times:

Formula:

Please Login or Register  to view this content.

Originally Posted by TMS

And another alternative without changing the times:

Formula:

Please Login or Register  to view this content.

but it is not showing correct result at 12pm

And the first one appears not to work correctly for 8am

Ho hum ....