I have a question about monthmath( My formula in the datasheet field properties needs tweaking to get the results I want – I have three fields to determine when something is eligible for destruction:
(1) Start - Field Properties = Type: Date; OutputPattern: MM/DD/YY; InPutPattern: __/__/__
(2) LifeCycle - Field Properties = Type: Number (integer)
(3) Results - Field Properties = Type: Date; OutputPattern: MM/DD/YY; InPutPattern: __/__/__
The Formula attached to Results is: monthmath(Start,12*LifeCycle)+1
This works fine for any date entered… except 12/31/YY (yy meaning any year)
For example, if I enter Start as 08/31/02 and then enter in LifeCycle 3; the Results = 09/01/05 (which is OK).
But… if I enter Start 12/31/02 and then enter LifeCycle 3; Results = 01/01/06 (when it should be 01/01/05).
Also is it possible to have a formula that calculates the year correctly plus adds one day to the Results – Example: Start: 08/31/02, LiveCycle: 3; Results: 09/02/05. Our life cycle is the number of years plus one day.
Thanks for any suggestions, Dusty