Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

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

Avatar

Level 6

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!

1 Accepted Solution

Avatar

Correct answer by
Level 10

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

View solution in original post

9 Replies

Avatar

Level 10

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")

Avatar

Level 6

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

 

Avatar

Level 10

Wrap it with an if expression

 

if (DateField1.isNull eq 0) then

    ;add the script here

endif

Avatar

Level 6

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!

 

nowackem_0-1647955545180.png

nowackem_1-1647955573140.png

nowackem_2-1647955606214.png

 

 

 

Avatar

Correct answer by
Level 10

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

Avatar

Level 6

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

Avatar

Level 2

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();
}
}

 

Avatar

Level 6

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

Avatar

Level 2

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

}

}