Creating a date calculation

[SharonR]

Hi everyone. I am in the process of creating a file for all of our customer contracts and I need a field that will calculate what we call the "Action Date". The action date is supposed to be 3 months before the contract expires. It sounds simple enough but I am having a complete brain malfunction. Basically what I need the field to do is automatically calculate what this date should be based on my "Contract Expiration Date" field. So if the contract expires on 12/31/11 I want the Action Date field to automatically be filled by the date 9/31/11. I did have a calculation that did this but I accidentally deleted it. The answer I was given on another forum was Month (Contract Expiration Date) - 3 Obviously since I am here this did not work at all. It isn't even filling an incorrect date into the "Action Date" field. It is just blank. Any help would be greatly appreciated.

Thanks!

Sharon

[Vaughan]

The action date is supposed to be 3 months before the contract expires... So if the contract expires on 12/31/11 I want the Action Date field to automatically be filled by the date 9/31/11

The first thing to understand is that "month" and "year" are not precise values. One "month" can be 28, 29, 30 or 31 days long. One "year" can be 365 or 366 days long.

Usually it's better to define the intention. By "3 months before" do you mean 90 days?

What will be the action date if the contract date is 30 May? It cannot be 30 Feb so should it be the last day in Feb or something else?

BTW There is no right and wrong here. It's a matter or working out the issues and deciding on a solution that meets the business needs.

[SharonR]

The first thing to understand is that "month" and "year" are not precise values. One "month" can be 28, 29, 30 or 31 days long. One "year" can be 365 or 366 days long.

Usually it's better to define the intention. By "3 months before" do you mean 90 days?

What will be the action date if the contract date is 30 May? It cannot be 30 Feb so should it be the last day in Feb or something else?

BTW There is no right and wrong here. It's a matter or working out the issues and deciding on a solution that meets the business needs.

Yes, 90 days would be better to use. So in my example where I added a day to September I would be looking for the action date to be the last day of September since the contract end date is the last day of December. Thank you.

[Raybaudi]

Try this calculation ( result Date ):

Let([

d = Day ( Contract Expiration Date ) ;

m = Month ( Contract Expiration Date ) ;

y = Year ( Contract Expiration Date )

];

Min ( Date ( m - 3 ; d ; y ) ; Date ( m - 2 ; 0 ; y ) )

)

[SharonR]

Try this calculation ( result Date ):

Let([

d = Day ( Contract Expiration Date ) ;

m = Month ( Contract Expiration Date ) ;

y = Year ( Contract Expiration Date )

];

Min ( Date ( m - 3 ; d ; y ) ; Date ( m - 2 ; 0 ; y ) )

)

Thank you so much!! It worked perfectly!