Monthly Time sheet

Question

I would like to make a monthly time sheet, so far I can input the working hours per day and it makes calculations based on dates, excluding holidays. The dates change automatically depending on the month and the year. 

What I would like to do is to calculate the total of working hours per week, and the result should be written always on the cells where the day is "Sunday". 

For example, for January 2018, I have to work 25 hours per week, that means that I should work 5 hours per day, from Monday to Sunday. But at the end of the week, I should have the total of hours that I should work and also the total of hours that I did work. 

How can I make a formula that calculates Total of hours to be worked and total of hours worked per week, excluding holidays and that changes automatically depending on the month and the year, and that the total is written on the row where "Sunday" is.

Date	Day	In	out	breake out	break in	Total hours that should be worked	Total worked hours per day	Total worked hours per week
1.	Mon.
2.	Tue.	09:30	15:00	10:00	10:30		5.00
3.	Wed.	10:00	14:00				4.00
4.	Thu.	10:00	17:00	13:00	13:15		6.75
5.	Fri.	12:00	15:00				3.00
6.	Sat.
7.	Sun.					20(it should deduct 5 hours, because the 1st of January is a Holiday)		18.75 (SUMPRODUCT(MONDAY TO SUNDAY) it should change depending on the month and the year
8.	Mon.	09:30	15:00	10:00	10:30		5.00
9.	Tue.	10:00	14:00				4.00
10.	Wed.	10:00	17:00	13:00	13:15		6.75
11.	Thu.	12:00	15:00				3.00
12.	Fri.	12:00	15:00				3.00
13.	Sat.
14.	Sun.					25		21.75
until the end of the month...

Thank you in advance for your help!

Answer 1

Hello,

for the weekly actual totals you could use a SUM() function that sums up the cells for the daily totals.

For the total hours that SHOULD be worked, how will Excel know that a day is a holiday? Holidays need to be marked in your table by something that can be recognized in a formula, i.e. NOT colour or bold font. You may need to add another column for "is holiday?" and put in a "y" for holidays, leave blank for regular days. 

Date	Day	is holiday?	In	out	breake out	break in	Total hours that should be worked	Total worked hours per day	Total worked hours per week
1	Mon.	y
2	Tue.		09:30	15:00	10:00	10:30		5
3	Wed.		10:00	14:00				4
4	Thu.		10:00	17:00	13:00	13:15		6.75
5	Fri.		12:00	15:00				3
6	Sat.
7	Sun.						20		18.75
8	Mon.		09:30	15:00	10:00	10:30		5
9	Tue.		10:00	14:00				4
10	Wed.		10:00	17:00	13:00	13:15		6.75
11	Thu.		12:00	15:00				3
12	Fri.		12:00	15:00				3
13	Sat.
14	Sun.						25		21.75
until the end of the month...

The formula in the "Should be worked" column is 

=IF(B8="Sun.",SUMPRODUCT((C2:C6<>"y")*5),"")

Adjust to suit your cell addresses. 

The formula for the weekly total is 

=IF(B8="Sun.",SUM(I2:I8),"")

Start both formulas in the row of the first Sunday and then copy down. You will always need seven data rows above the formula, otherwise you'll get errors, so when you start a new month, you may need to keep the last few days of the previous month, so the table always starts on a Monday.