I have a list of dates in F5:G14, column F is a list a start dates for working abroad, and column G is a list of end dates, return to the UK.
Dates within this table can range from the previous year, ie F5=01/11/2023, and trips can and usually do straddle months, ie G5 = 10/02/2024.

Below I have another table to calculate monthly wages due.
Months are in A21:A32.
Days worked is in B21:B32, this is what I am trying to write a formula for.
I currently have this formula below in C21 to try and extraplotate only the days worked in January but I am not getting the correct figure, I am returning "0" and not "31" as expected from the dates in F5 & G5
Another factor to be taken into account, is that there may be multiple trips within the same month, so hence why the formula has selected the entire table of dates

=SUMPRODUCT((MONTH($F$5:$F$14)<=MONTH($A21))*(MONTH($G$5:$G$14)>=MONTH($A21))*(YEAR($F$5:$F$14)<=YEAR($A21))*(YEAR($G$5:$G$14)>=YEAR($A21))*(IF((MONTH($F$5:$F$14)=MONTH($A21))*(YEAR($F$5:$F$14)=YEAR($A21)),DAY($G$5:$G$14)-DAY($F$5:$F$14)+1,IF((MONTH($G$5:$G$14)=MONTH($A21))*(YEAR($G$5:$G$14)=YEAR($A21)),DAY($G$5:$G$14),IF((MONTH($F$5:$F$14)<MONTH($A21))*(MONTH($G$5:$G$14)>MONTH($A21)),DAY(EOMONTH($A21,0)),0)))))

So the long and short of it, is that I am trying to extrapolate only the days between the start and end dates in the top table and only count the days occuring between those dates for each month, Jan-Dec in the bottom table