I'm trying to calculate the total fee between two dates of a service
with a monthly fixed fee. This means that the fee per day is different
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 like
Datedif(data1;date2;"d")/30,4375 but this uses an average number of
days per month and doesn't produce an exact enough result.

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

Does anyone know a simple trick to solve this?

Thx


--
effem
------------------------------------------------------------------------
effem's Profile: http://www.excelforum.com/member.php...o&userid=16112
View this thread: http://www.excelforum.com/showthread...hreadid=275586

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

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


--
Alex Delamain
------------------------------------------------------------------------
Alex Delamain's Profile: http://www.excelforum.com/member.php...o&userid=11273
View this thread: http://www.excelforum.com/showthread...hreadid=275586

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

"effem" wrote in message
...

I'm trying to calculate the total fee between two dates of a service
with a monthly fixed fee. This means that the fee per day is different
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 like
Datedif(data1;date2;"d")/30,4375 but this uses an average number of
days per month and doesn't produce an exact enough result.

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

Does anyone know a simple trick to solve this?

Thx


--
effem
------------------------------------------------------------------------
effem's Profile:
http://www.excelforum.com/member.php...o&userid=16112
View this thread: http://www.excelforum.com/showthread...hreadid=275586

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.