Principle behind a Date Calc

[LaRetta]

Hi Everyone!

I needed to use Replace Contents (on a stand-alone system) through a set of newly migrated data to set a date field (DateTherapistPaid) with a date which is the last day of the *next* month based upon its ServiceDate, but if the ServiceDate is less than 8/1/2002, set the field with 9/30/2002. I created an unstored calc through field definition to view the results before I proceeded ... and also backed up.

I assumed I had to de-construct the date and re-construct it again? I searched Forum and to get the *last day* of a month, I need to find the *first* of the next month and -1? So I jumped 2 months forward. This calc does work.

The reason for my post is this: I want to learn the optimum way of utilizing calculations and my *solution* appears to be clunky. Was there a better approach I could have considered?

Case (ServiceDate < Date(8,1,2002), Date(9,30,2002),

ServiceDate>= Date(8,1,2002), Date(Month(ServiceDate)+2), Day(1)-1, Year(ServiceDate)

Formulas (particularly date formulas) are very difficult for me and I appreciate any feedback as I am attempting to understand the principles involved not just cut & paste someone else's solution.

[BobWeaver]

That formula looks pretty good, although you don't need to put in the test for the second case: ServiceDate>= Date(8,1,2002), since any date that is not less than 8/1/2002, must by default be greater than or equal to 8/1/2002. So you can simplify the formula down to:

Case (ServiceDate < Date(8,1,2002), Date(9,30,2002), Date(Month(ServiceDate)+2), Day(1)-1, Year(ServiceDate))

[LaRetta]

Oh that makes sense! Thanks Bob. So the remainder of the Service Dates (>=8/1/2002) (result2) actually *becomes* the default result. I remember thinking that there would be no records left. I don't NEED a default result if there are no records remaining to deal with, correct?

Case ( test1 , result1 [ , test2 , result2 , default result]... )

Oh, here's another formulae I like now!!