Calculate Number of hours worked in a week

[ismailshajji]

Hello friends,
I am working on a timesheet sreadsheet which i have developed using this great forum. i am currently struck on my last part.

My problem:-
Coulum I consists of number of hours worked in a week which is calculated from the column H The total is calculated on every sunday of the week.
i have created a function where in if if change the year in cell C2 the days move and the problem is the formula to calculate the total hours is fixed to a particular cell because of which i am not getting accurate hours if i change the year.
i am attaching my spreadsheet for referance.

thank you for your help.

[protonLeah]

Use helper column (L) with WEEKNUM()
Then in I5:I35

Please Login or Register  to view this content.

[ismailshajji]

Thank you so much protonLeah for your prompt response.
Great, how did you add sliding tool for chaning the year and month it looks great i would like to use this functionality in different sheet.

[protonLeah]

Those are Form Control 'SCROLL BARS':
On the Ribbon Developer Menu,
Select 'Insert'
Select Scroll Bar from the Form Controls sub menu.
Grab one of the corners and adjust the size and orientation desired.
Right click the scroll bar, enter the min and max values desired and the cell to link it to.

[ismailshajji]

Thank you so much my friend.
One final request on the sheet you have wonderfully created the formula which calculates total number hours worked per week on sundays.
is it possible to caculate the residual hours ie on the last day of the month if the week doesnt end with the sunday? for example in our spread sheet the month of october 31 doesnt end with Sunday so is it possible to add the hours for the days 28,29,30, and 31st bearing in mind the days will change as soon as we change the year and the month.
thanks my friend for your help.

[protonLeah]

maybe:

Please Login or Register  to view this content.

[ismailshajji]

maybe:

Please Login or Register  to view this content.

thank you my friend it definitely worked , this formula helped enter the total hours worked in cell i36, is it possible for i35 to display the total hours worked in that week in this case it should be SUM(H32:H35) ie 30 hours. sorry for the trouble.

[protonLeah]

Sorry, my mistake. I changed the font color to white in column I for the grey rows, so you can just change it back to auto or black. The results are already calculated with the last formula.

[ismailshajji]

Sorry, my mistake. I changed the font color to white in column I for the grey rows, so you can just change it back to auto or black. The results are already calculated with the last formula.

You are Genius thank you so much my friend.
have a wonderful day and good night.

[protonLeah]

You're welcome