Formula which can calculate hours and days in a week

https://onedrive.live.com/redir?resi...nt=photo%2cpng

Hello every one. I have attached a screen shoot of my work sheet. On the worksheet, all data are from other worksheet. My column A and Row 1 and 2 are all formula. I want to use those data to do two things:
1. How can I calculate how many hours and days each person has worked during a week (from Sun to Sat). I want those are formulated because once I change my raw data then this sheet will be charged too. And each time, I will have different week days in different column. For example: when I change to period between Feb 11 to March 25th the week day column are changed accordingly.
2. How can I get the result from the data sheet to show me that who is working on which date.
For example, a function can show me all the people who working on Feb 1st, Monday or any other workday as I requested.

I use Filter function, but it doesn't work that great and smooth. I couldn’t find other better way to achieve my goals. Please help me out.

Thank you and will do

I don't know how to attach my work book to the file. when I click attachment icon it only shows a blank line. there are no selection of my computer root.
And therefor I couldn't upload my file. Is someone can help me with this?

Since I couldn't upload my workbook, I try to explain my question as follow. Hope someone know how to do it and help me on this one
I have a work sheet like following. All datas are formulated and links from other working sheet . once I paste new raw data on that working sheet, this sheet will change too.the first column is employee names and and rest of column are showing how many hours they worked that day.

Jan 26 Jan 27 Jan 28 Jan 29 Jan 30 Jan 31 Feb 1 Feb 2 Feb 3
Thu wed Thu Fri Sta Sun Mon Thu Wed
Name
Bob 2.5 3 2 5 6 8 6 5
Bill 3.5 4 2 5 5

I want to using above data to achieve two goals.
1. How can I calculate how many hours and days each person hasworked during a week (from Sun to Sat). I want those are formulated becauseonce I change my raw data then this sheet will be charged too. And each time, Iwill have different week days in different column. For example: when I changeto period between Feb 11 to March 25th the week day column are changedaccordingly.
2. How can I get the result from the data sheet to show me that who is workingon which date.
For example, a function can show me all the people who working on Feb 1st,Monday or any other workday as I requested.
the results look like this
name Total working hours Jan 26-30 Total days Jan 26-30 Total working hours Jan 31-Feb 6
Bob 18.5 5 19
Bill 14.5 4 5

Date working employees
Jan 26 Bob
Bill
Jan 27 Bob
Bill
Jan 28 Bob


I use Filter function, but it doesn't work that great andsmooth. I couldn’t find any other way to achieve my goals. Please help me out.
Thank you so much

hi can you upload an example spreadsheet

To upload a file

Click Go Advanced
Then Attachments
Then in The Manage Attachments Window
Click Add File
Browse for file From your PC
Once Selected Then Upload
The Selected File should be shown in the bottom left corner (with a Tick in)
Then Click Insert Inline
Then Done

Thank you for your reply but I couldn't upload the file. when I use attachment icon, it won't showing anything.just blank line.I don't know why maybe my internet setting is wrong.

I have reported the attachment issue to admin, hopefully they will be able to resolve this soon.

Thank you very much. I hope I can upload my workbook so genius guy like you can help me out

I attached my work book now. Thank you for your instruction and reply
I will change my Raw data sheet each time according to different pay period. and that is why I want to use a function which can summaries data according to weekdays
Since my period time sometime across three weeks ( two partial weeks and one whole week). I want the excel identify the partial weeks and the whole week then sum the data accordingly.
Please see my worksheet for detail.

Thank you so much.

Hi Thank you for get back to me. your formulas are much more cleaner. I used the both formulas and they work like a charm. much more appreciated
But in your formulas it only pull the "driver hours". in my raw data there are some employee have hours in other hours row as well. the hours I use in my Calculation sheet should be "Subtotal hours". Could you edit your formulas a bit so I can pull subtotal hours instead of driver hours. and yes i don't use the same reference columns for the hours because I want to caculat the weekly hours which star from Sunday to Sat and it stars again for each one. for example in this work sheet it come across three weeks. I have to calculate them separately which are Tue , Jan 26 to Sat Jan 30 ; Sun Jan 31 to Sat Feb 6; Sun Feb -7 to Wed Feb 10. and when the time period change the date of week in per column changes too. and each time I have to manually change the column reference to caculat different weekly hours. and this is my final issue to find out a formula can sum the weekly hours according to the date of the week.
by the way, I couldn't get:=IFERROR(INDEX(range-with-names,small(if(range-with-dates=cell-with-date,row(range-with-dates)-how-many-rows-in-heading),ROWS($A$1:A1))),"")
entered using CTRL SHIFT ENTER" this formula to work.
Thank you so much for all your help

I don't know if I ask too much favor of not. Could you help me out on the duplicated name too? In the raw data for the some reason we have to put same employee into different cells.they all have different hours under each name. for example Adam has 51 hours in one row and 30 hours in the other row, his total hours for the period is 81 hours. Is there a formula can help me sum all different hours together for the duplicated names.

Thank you for your reply. the sample sheet I gave to you doesn't have those duplicated names. but in my real working sheet,it has a lot of duplicated names. Sorry about the trouble.
Can we assume there are duplicated names in row 15 ? I tried it on my sheet and the new problem occurred with the formula you sent to me. it can't differentiate the duplicated name and return the same value for both names.but they are really have different set of hours for each name.

no, I want to adding together.