How do I find a float for the number of months between 2 days (Google Sheets)?

I'm trying to use google sheets to calculate back pay for benefits that evaluate on a pro-rated monthly basis. There is a static number of months available, and I want to track how many have been used so far. Ideally, I'd use one cell for the start date of the relevant period, one cell for the end date, and then one cell for the number of months with a floating point number instead of an integer. The problem is that the benefits evaluation doesn't assess a month as a set number of days, it assesses the percentage of each month, so for example, 1 day in January has a different weight than 1 day in February. I want to be able to evaluate, for example, the number of months between 1/30/23 and 5/24/23, all in one cell. I can't seem to find any relevant formulas, and I'm not experienced with writing custom functions, though I'm willing to learn. Are there existing formulas that can do what I'm trying to do here?

• Tried using DAYS() and dividing the result by 30, which didn't account for varied month lengths.
• Tried using =DAYS(J2,I2-1)/(DAYS(EOMONTH(I2,0),EOMONTH(I2,-1))) for each set of start and end dates I[N] and J[N] spanning a max of one month each, then using SUM to get the number of months in the period. This works, but not in one cell.

Demo Sheet , showing valid values, the calculations needed for cells D2-D3 are performed on an expanded set of cells G2-I11.

In your demo sheet, I think H6 should be 31-Jan-23 instead of 30-Jan-23. If so, then my numbers match yours.

To answer your question, the trick would be

• to look at only the first and last month
• and calculate the proportion of only those two months
• all intervening months count as 100%.

So the single-cell formula works out to the following (where the start date is in cell I35 and the end date is in cell J35):

=(MONTH(J35)-MONTH(I35)-1)+(DAYS(EOMONTH(I35,0),I35-1)/(DAYS(EOMONTH(I35,0),EOMONTH(I35,-1))))+(DAYS(J35,EOMONTH(J35,-1))/(DAYS(EOMONTH(J35,0),EOMONTH(J35,-1))))

Please see this sheet for the details that explain the three parts of the formula:

1. Intermediate months
2. First fractional month
3. Last fractional month

The sheet has view-only rights. To anyone who wants to edit it, please make a copy.

You may try:

=index(let(Λ,eomonth(sequence(days(C2,B2-1),1,B2,1),),
       round(sum(map(unique(Λ),lambda(Σ,countif(Λ,Σ)/day(Σ)))),2)))

• there's a deviation of output in row_3 4.06 compared to your expected output of 4.03 since you seem to have taken the end of month date as 30-Jan and not 31-Jan within your breakup calculation part (Cell_I6)