Perform Calculation upon date range

[Kimmie]

Hi All!

I am here yet again for all your wonderful help! *I mean that sincerely! Anyway, I am trying to calculate a field depending on a date range. I searched up & down for an answer which makes me think its SOOO simple that no-one has needed to ask. If thats the case, oh well, here I go! Here is the calculation I created:

If ( Cancellation Date = "11/1/2007" & "..." & "3/30/2008"; Total Cancellation Amount * .25 ; " " )

I have 3 different sets of dates that I need to calculate from. I have a cancel date field. Not sure what I'm doing wrong? I even tried changing the cancel date field to text. Help!

[aldipalo]

The problem you are having is you are trying to set up date range within a calculation. Use date ranges for searching the database.

Without knowing where you are using this (Script or calculation field) try something like this:

Calculation Field or Auto enter calc.

Case(

Date Cancelled>= Date Sold and Date Cancelled <= Date Sold+90 ; Price * .25;

" "

)

Script

If(

Date Cancelled >= Date Sold and Date Cancelled <= Date Sold +90)

Set Field(Cancellation Amount;Price * .25)

EndIf

This allows the desired result in either without having to 'hard code' the dates.

Edited July 21, 2008 by Guest

[Kimmie]

Hi aldipalo,

That does work but the problem is that a lot of contract dates are different, they span the whole year. The first contract date is the third week in November and can range from then to the second week of November of the following year. The cancellation dates are specific to the third week in November to the end of March. If anyone cancels in that time period it is an automatic .25 they owe of their contract even if they sign on March 29th. From the beginning of April through the end of June would be .50% they owe of their contract and from the beginning of July until the show in November they owe 100% of their contract. I have been trying to re-arrange the calculation somehow but still can't seem to find an answer. Thank you in advance for all your help!!