Completion date calculated based on a start date and an amount from a number field. | Community
Skip to main content
nowackem
nowackem
Level 5
March 18, 2022
Solved

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

  • March 18, 2022
  • 2 replies
  • 1880 views

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!

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by radzmar

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!

 

 

 

 


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

2 replies

radzmar
radzmar
Level 10
March 20, 2022

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
    nowackemAuthor
    Level 5
    March 21, 2022

    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
      radzmar
      Level 10
      March 21, 2022

      Wrap it with an if expression

       

      if (DateField1.isNull eq 0) then

          ;add the script here

      endif

        nowackem
        nowackemAuthor
        Level 5
        March 22, 2022

        @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
          Missile64
          Level 2
          March 24, 2022

          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
            nowackemAuthor
            Level 5
            March 24, 2022

            @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)
            }
            }

              Terms & ConditionsAccessibility statement

              Loading footer...