Have to calculate a price depending on the start and end date.

**Misas** · 06-29-2012, 08:07 AM

Hello guys

I have a problem.

I am trying to figure out a single formular that gives me a price (number) depending on start and end date.

Let me elaborate.

The situation is that some (a lot of) people have forgot to pay their subscriptions for a year. The price varies each year and I am trying to calculate what each subscriber owes.
The payment is for each month and the subscription starts on the date the subscriber subscripes.

Examples:
Start dates: End dates:
26-04-2011 25-04-2012
05-08-2010 05-08-2011
13-04-2011 12-04-2012
05-01-2011 05-01-2012

Each enddate is calculated by '=startdate+365' and note that 2012 is a leapyear.

The prices are:
2010: 424,-
2011: 442,-
2012: 451,-

So basically I need to figure out the price from startdate up to the end of that year and then again from the begging of the next year up til the enddate.

I have been able to figure out the number of days between the two dates, but how to add the prices in to that is beyond me. Especially with only one formular.

I hope you guys can help.
Thanks in advance.

**Ace_XL** · 06-29-2012, 09:08 AM

Try this

=SUM((DATE(YEAR(A1),12,31)-A1)/365*CHOOSE(YEAR(A1)-2009,424,442,451),(B1-DATE(YEAR(B1),1,1))/365*CHOOSE(YEAR(B1)-2009,424,442,451)) where A1 is the start date and B1 is the end date.

**Misas** · 06-29-2012, 09:28 AM

Thank you for the quick reply Ace_XL

I copy pasted what you gave me into an example sheet on C1.

It told me there was something wrong at ',12,31' and again at '2009,424,442,451'.

Quick side-note: I am actually using Excel 2007 despite it saying 2003 on my profile. I'll change that right away. //changed

**Ace_XL** · 06-29-2012, 09:41 AM

Please refer attached. I hope the date format is a mm/dd/yyyy format

**Misas** · 06-29-2012, 10:35 AM

Thank you Ace_XL

I am considering this closed.

I looked at it and understood what you did. I appreciate your help!

I even modified it to fit even more perfectly

Thank you once again!