Completion date calculated based on a start date and an amount from a number field.

[nowackem]

Hi all,

Has anyone written a script to calculate a completion date (date field) based on a user entered start date(date field) + user entered number of days(number field)? If so, do you mind sharing or point me to the proper thread?

(Use case: A contractor chooses a start date for a job, and then enters the number of days the project will last, then the ending date is calculated based on the starting date + job length...we do not need to exclude the weekends.)

Thanks!

[radzmar]

You can use FormCalcs Date2Num() method to convert a date into a number and Num2Date for the other way. Here an example:

var nStartDate = Date2Num(Datefield1.formattedValue, "MM/DD/YYYY")
var nAddDays = ProjectLengthField.rawValue
var nEndDate = Sum(nStartDate, nAddDays)
DateField2 = Num2Date(nEndDate, "MM/DD/YYYY")

[nowackem]

Good morning! This does work as FormCalc script in the Calculate event. I need to take this one step further and keep the End Date blank until the Start Date and the Project Length objects are entered. I tried to take this script into the Calculate event as JavaScript but I'm not getting it to work. I'm sure I'm missing something silly. Maybe you can see what I'm doing wrong. Please see link below:

https://drive.google.com/drive/folders/1EASnF9uPd6PP69_SgfwuPojJJuZOBd-L?usp=sharing

[radzmar]

Wrap it with an if expression

if (DateField1.isNull eq 0) then

    ;add the script here

endif

[nowackem]

After wrapping it in the code you suggested, It does the calculation however it displays a zero in the End Date before any calculations are done and it still populates a value in the End Date after I enter a Start Date but before I enter the Project Length. So How do we fix that? I don't want any values in the End Date before both the Start Date and Project Date are entered. Thank you!

[radzmar · Accepted solution]

Well, that's because the calculation script returns false (= 0) if the expression is not met.

var cResult = ""
if (DateField1.isNull eq 0) then
    var nStartDate = Date2Num(Datefield1.formattedValue, "MM/DD/YYYY")
    var nAddDays = ProjectLengthField.rawValue
    var nEndDate = Sum(nStartDate, nAddDays)
    cResult = Num2Date(nEndDate, "MM/DD/YYYY")
endif
DateField2 = cResult

[nowackem]

@radzmar 

Thank you for your reply. I have a similar script with the same three types of objects that does not return a zero like this. Below is that script but it calculates in months instead of days. Can you help me tweak this one to make it calculating days instead of months and accommodates the different month lengths and leap years?

Field Names:

Start Date(date field) = tdStartDate

End Date(date field) = tdEndDate

Contract Term(number field) = numTerm

JavaScript(set in the calculate event in the End Date field):

if(tdStartDate.rawValue!=null){
if(numTerm.rawValue!=null){
var d = new Date(tdStartDate.rawValue);
var dd = new Date(d.setMonth(d.getMonth() + numTerm.rawValue));
var newDate = new Date(dd.setDate(dd.getDate()+1));
this.rawValue = newDate.toDateString();
}
}

[Missile64]

This coding is what I have been looking for, thanks

I have modified it a little so that it populates the End Date field with the "Start Date +5 years", I was wanting the this format e.g..  24 March 2027 (DD MMMM YYYY) .

The issue a cannot seem to fix is this "this.rawValue = newDate.toDateString();", the toDateString() sets the end date format to "Thu Mar 24 2027".

Do you know how I can change the code so the end date format is "DD MMMM YYYY".

Thanks

if(SDSDate.rawValue!=null){
if(Plus5Years.rawValue!=null){
var d = new Date(SDSDate.rawValue);
var dd = new Date(d.setFullYear(d.getFullYear() + Plus5Years.rawValue));
var newDate = new Date(dd.setDate(dd.getDate()+1));
this.rawValue = new Date.toDateString();
}
}

[nowackem]

@Missile64  I too was trying to figure out how to format the date but mine was MM/DD/YYYY, I at least got this to work this morning. I know its not exactly what you were looking for but maybe it will spark a conversation where someone can help. (this is in the calculate event, language: JavaScript)

if(tdStartDate.rawValue!=null){
if(numTerm.rawValue!=null){
var d = new Date(tdStartDate.rawValue);
var dd = new Date(d.setMonth(d.getMonth() + numTerm.rawValue));
var newDate = new Date(dd.setDate(dd.getDate()+1));
this.rawValue = (newDate.getMonth() + 1) + "/" + newDate.getDate() + "/" + newDate.getFullYear()
this(newDate)
}
}

[Missile64]

@nowackem, thanks very much for helping me out. I was able to get the format I wanted, albeit without a leading "0" on single digit days.

The below code gives me this format - D MMMM YYYY

For what I am doing, it is good enough

Thanks again

if(SDSDate.rawValue!=null){

if(Plus5Years.rawValue!=null){

var d = new Date(SDSDate.rawValue);

var dd = new Date(d.setFullYear(d.getFullYear() + Plus5Years.rawValue));

var newDate = new Date(dd.setDate(dd.getDate()));

const months = ["Janurary", "Feburary", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"]; t

his.rawValue = newDate.getDate() + " " + months[newDate.getMonth()] + " " + newDate.getFullYear();

this(newDate)

}

}