Sum of only monthly hours

week Monday - Friday

if so
wtat these dates mean?: 16.07.2019 29.07.2019

This is a random date I have included because employee join in any day of month.

Assuming that From 16/07/2019 to 29/07/2019 as in your example, and From 01/08/2019 to 03/08/2019.

Use the function Networkdays and multiply it by 8 hours per day in the same formula, you will have 96 hours in total for July through 3 aug, 2019.

Hope its solved, let me know if its not...

Solved file is attached.

Here's another suggestion. Create a table that reviews the date of the week and allocates a percentage of the total hours to each month given the day in the date. Once allocated, multiple each month by the percent allocation in order to get a total hours for each month. Then create a separate table to sum the hours per month using sumif formula. Details below. I tried to upload a workbook as well so hopefully it came through.

Create Table 1 per details below. I've named each column and then used the name in the formulas

Column 1: Week
Column 2: Hours
Column 3: Month 1 (formula is =MONTH((Week)
Column 4: Month 1 Allocation (formula is =IF(DAY(Week)>7,1,DAY(Week)/5))
Column 5: Month 2 (formula is =IF(Month 1 Allocation=1,"",MONTH(Week)-1)
Column 6: Month 2 Allocation (formula is =IF(Month 1 Allocation=1,0,1-Month 1 Allocation)
Column 7: Month 1 Total (formula is =Hours*Month 1 Allocation
Column 8: Month 2 Total (formula is =Hours*Month 2 Allocation

Now create your output (Table 2)
Column 1: Month (manual input with a number for the month. So for this example, Row 1 would be "7" for July and Row 2 would be "8" for August. In the next formula, J9 is
Column 2: =SUM(SUMIF(Table1 (Month 1),Month,Table1(Month 1 Total),(SUMIF(Table1(Month 2),Month,Table1(Month 2 Total))))

Thanks for the reply.

The 8 hours not fixed every day because people take leave. The problem is only last week of month because some days go to next month.

So whatever hours in last it will divide by 5 days and split in months.

Hi Oniwlmh,

Thanks for the reply.

I can't include more table in report because I just give example of 3 weeks but in originally I have multiple weeks so its very time consuming to include this extra table.

Thank You Bo_Ry the formula working perfectly..

Thanks JeteMc for the help.

Below "Bo_Ry" formula working perfectly. I will get back to you if I faced any issues.