May 26 2021 12:43 PM
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.
May 26 2021 01:55 PM - edited May 26 2021 01:55 PM
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)