4 Replies Latest reply on Mar 9, 2015 12:35 AM by aishasuzan101

# convert

How to convert this excel formula into calculation script in pdf?

=DATE(YEAR(M9)+L9,MONTH(M9),DAY(M9))

• ###### 1. Re: convert

We'll need more information. If the value in M9 is supposed to be a date, what format would it be? For example, yyyy/mm/dd

Is the idea that the formula is taking one date and adding a certain number of years to it?

• ###### 2. Re: convert

yes. a date and adding number of years.

• ###### 3. Re: convert

// Excel =DATE(YEAR(M9)+L9,MONTH(M9),DAY(M9))

function GetField(cName) {
// get field object with error checking;
var oField = this.getField(cName);
if(cName == null) app.alert("Error accessing field " + cName + "\nPlease check the existence of the field and its name.", 1, 0);
return oField;
} // end GetField function;

function Scand(cForamt, cDateString) {
// convert date string to a date object with error checking;
var oDate = util.scand(cFormat, cDateString);
if(oDate == null) app.alert("Error converting " + cDateStirng + " with format of " + cFormat +"\Please check the format of the date field", 0, 1);
return oDate;
} // end Scand function;

// custom JavaScript calculation for the target field;
// format for dates;
var cFormat = "d-mmm-yyyy";
// field name for formatted input date;
var cDateField = "M9";
// field name for the number of years to add;
// get the field objects;
var oDateField = GetField(cDateField);
event.value = ""; // clear the target field;
// convert date string to date object;
var oDate = Scand(cFormat, oDateField.valueAsString);
// get the full year from the date object;
var nFullYear = Number(oDate.getFullYear());
// add number of years to the full year;
// rest the date object for the new year value;
oDate.setFullYear(nFullYear);
// set the target field with the new date;
event.value = util.printd(cFormat, oDate);

The script could be shortened by not declaring variables for each step. Or creating a function to read the values compute and return the new date string.

• ###### 4. Re: convert

Hi.

this is what I entered in the custom calculation script:

// DestroyDate1 =DATE(YEAR(ProjClosureDate1)+RetentionPeriod1,MONTH(ProjClosureDate1),DAY(ProjClosureDate 1))

function GetField(cName) {
// get field object with error checking;
var oField = this.getField(cName);
if(cName == null) app.alert("Error accessing field " + cName + "\nPlease check the existence of the field and its name.", 1, 0);
return oField;
} // end GetField function;

function Scand(cFormat, cDateString) {
// convert date string to a date object with error checking;
var oDate = util.scand(cFormat, cDateString);
if(oDate == null) app.alert("Error converting " + cDateString + " with format of " + cFormat +"\Please check the format of the date field", 0, 1);
return oDate;
} // end Scand function;

// custom JavaScript calculation for the target field;
// format for dates;
var cFormat = "d-mmm-yy";
// field name for formatted input date;
var cDateField = "ProjClosureDate1";
// field name for the number of years to add;
// get the field objects;
var oDateField = GetField(cDateField);
event.value = ""; // clear the target field;
// convert date string to date object;
var oDate = Scand(cFormat, oDateField.valueAsString);
// get the full year from the date object;
var nFullYear = oDate.getFullYear();
// add number of years to the full year;
// rest the date object for the new year value;
oDate.setFullYear(nFullYear);
// set the target field with the new date;
event.value = util.printd(cFormat, oDate);

THE ONLY PROBLEM I AM FACING NOW IS THAT THE "DestroyDate1" field is showing the current date. It should be empty prior to the calculation.