Need a formula for months of a year from a given date range

Copper Contributor

I have the following in my spreadsheet:

 

Hours/Month(a)        Start Date(b)       End Date(c)      2020     2019     2018    2017      2016
        40                         9/1/2017             9/1/2019

 

I need need a formula that will take the year, look at the two dates, and then multiple the number of months in the year presented times the hours/month. Is this possible? I am trying to calculate the hours a person may have worked in a year from the date range provided.

 

1 Reply

@LadyMorbid7 

In D2:

=$A2*IFERROR(DATEDIF(MAX(DATE(D$1,1,1),$B2),MIN(DATE(D$1+1,1,1),$C2),"m"),0)

Fill to the right (and if applicable, down)

 

S0451.png