Difference between two dates in months with decimals

[effem]

I'm trying to calculate the total fee between two dates of a servic
with a monthly fixed fee. This means that the fee per day is differen
depending on the month (or even year if it's February).

Example...

Assume the monthly fixed fee is 1000.

From 05 March (27 days to count) to 20 April (20 days to count
the calculation would be: 1000 * (27/31 +20/30) = 1537,63
From 05 April (26 days to count) to 20 May (20 days to count)
1000 * (26/30 + 20/31) = 1511,83

I know it's possible to use something lik
Datedif(data1;date2;"d")/30,4375 but this uses an average number o
days per month and doesn't produce an exact enough result.

The problem is to find the exact number of months WITH decimals t
multiply the fee with... (keeping in mind also that it can be more tha
12 months).

Does anyone know a simple trick to solve this?

Th

 

[Alex Delamain]

With the period start date in A1 and end date in B1 the following give
the answers you want.

=((DAY(EOMONTH(A1,0)-A1+1))/DAY(EOMONTH(A1,0))+DAY(B1)/DAY(EOMONTH(B1,0)))*100

 

[Bernie Deitrick]

effem,

With your start date in A1, and your end date in B1, the formula

=((YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)-1+(DATE(YEAR(A1),MONTH(A1)+1,0)
-A1)/DAY(DATE(YEAR(A1),MONTH(A1)+1,0))+(DAY(B1))/DAY(DATE(YEAR(B1),MONTH(B1)
+1,0)))*1000

will account for both year and month differences and month length
differences. Note that this formula will line wrap in the message, so you
will need to remove the extra line breaks.

Note that you may need to add an additional day to this term

DATE(YEAR(A1),MONTH(A1)+1,0)-A1

becoming

DATE(YEAR(A1),MONTH(A1)+1,0)-A1 +1

depending on whether the first day counts or not.

HTH,
Bernie
MS Excel MVP

 

[Daniel.M]

Hi Bernie, effem,

With your start date in A1, and your end date in B1, the formula

=((YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)-1+(DATE(YEAR(A1),MONTH(A1)+1,0)
-A1)/DAY(DATE(YEAR(A1),MONTH(A1)+1,0))+(DAY(B1))/DAY(DATE(YEAR(B1),MONTH(B1)
+1,0)))*1000

Also
=(DATEDIF(A1,B1,"m")-DAY(A1)/(32-DAY(A1-DAY(A1)+32))+DAY(B1)/(32-DAY(B1-DAY(B1)+
32)))*1000

Regards,

Daniel M.