Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]() 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 |
#2
![]() |
|||
|
|||
![]() 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 |
#3
![]() |
|||
|
|||
![]()
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 |
#4
![]() |
|||
|
|||
![]()
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
due dates | New Users to Excel | |||
I get wrong dates when i paste from a different sheet into a new s | Excel Discussion (Misc queries) | |||
Calculate months and years | Excel Discussion (Misc queries) | |||
How do I display months and years between two dates | Excel Discussion (Misc queries) |