Calculate hours per month based on Start and End Date

[Dave1780]

Hello

I have a simple set of data with Start Dates and End Dates and hours. Assuming the hours are equally spread between the working days between the start and end dates, I am looking for a formula that calculates the number of hours in each calendar month. I was thinking of making a column to the table for each month I am interested in that calculates the total of the hours per line per month.


StartDate EndDate Hours
30/07/2020 31/08/2020 13.8
30/07/2020 5/01/2021 136.8
18/08/2020 24/08/2020 72.0
25/08/2020 31/08/2020 48.0
1/09/2020 15/10/2020 326.7
16/10/2020 16/11/2020 26.4
16/10/2020 13/12/2020 147.6
16/10/2020 5/01/2021 278.4
16/11/2020 29/11/2020 60.0
14/12/2020 5/01/2021 10.2

So the results for the above data set would be:
July 2020 - 3.6 hours
August 2020 - 157.80 hours
Septeber 2020 - 244.20 hours
Ooctober 2020 - 240.90 hours
November 2020 - 274.80 hours
December 2020 - 178.80 hours
Jan 2021 - 19.80 hours

[bebo021999]

Could you tell us how to calculate for one month,i.e, Jul 2020?

I came up with:
Task 1: total day: 31/08-30/7+1= 33 days ; hour each day = 13.8/33=0.42 = > day of Jul ( 2 ) * 0.42 = 0.84
Task 2: total day: 5/01/2021-30/7+1= 160 days ; hour each day = 136.8/160=0.86 = > day of Jul ( 2 ) * 0.86 = 1.71
Total Jul: 2.55 hours
You expect: 3.6 hours

Show me where I were wrong.

[hrlngrv]

I assume by hours are equally spread that you mean you divide hours in D2:D11 by the workdays between dates in B2:B11 and C2:C11. If so, with July 2020 in cell L1, try

M1:

Please Login or Register  to view this content.

Expand with

L2:

Please Login or Register  to view this content.

Fill M1 down into M2, then select L2:M2 and fill down as far as needed.

[kvsrinivasamurthy]

Post deleted

[kvsrinivasamurthy]

In F2 then copied across

Please Login or Register  to view this content.