Date Calculation: calculate x minus days from date entered on form

[Teresa Harden]

Hi, I have searched the forums, so I apologize if I missed this, but I copied a few, and have not got this to work.

I am creating a “simple” form (actually, a ‘wizard’) where people can see all their ‘deliverables’ due based on a date field they entere (Deploy Date).
I have Deploy date (date field)
I have 8 “due by” date fields (read only). (the phases/deliverables)

The user will enter the deploy date, tab out, and I’d like the other dates to populate. I don’t even care if the date falls on Saturday/Sunday – I just need it to calculate!!

This form will not be allowed to ‘save’. It’s just a quick glance to ‘count backwards’ for multiple phases.

Any help would be GREATLY appreciated, I spend a few hours on this today an elicited other DFFS users and we could not figure it out.

[Keith Hudson]

SharePoint will do the heavy lifting easily for you using calculated columns. However, since calculated columns are not calculated until you save the item, and since they are not in the field dropdown picker on a DFFS form (if I’m wrong on either of those two points, someone please correct me) one possible solution is to NOT use DFFS.

You could build a simple SharePoint list with a single date field (make sure the Title field is not required), build a calculated date field for each due by date, and build a Datasheet (aka Quick Edit) view of the list where the user simply enters their Deploy Date, then clicks on the empty line below, and the calculated dates will all appear as soon as the record is saved.

In order to not clutter the list (since the records do not need to be saved long term) you could build a SharePoint Designer workflow that starts as soon as an item is created, pauses for 10 minutes (or 10 hours or 10 days – whatever interval you want) and then deletes the item from the list.

In order that the view is always blank when someone comes to it, filter the view to only show items where Created is equal to [today] + 1.

That approach may not meet your needs, but I thought I would suggest it as a possible simple solution in case it DOES meet your needs.

DFFS is amazing, but even native SharePoint can do ‘magical’ things when we apply a little creativity.

(Side note: if this turns out to be the exact solution you need, don’t fault yourself for not thinking of it earlier. I too spent several hours trying to develop a tool once using DFFS, and finally realized that a simple Quick Edit view would do everything I needed simply and elegantly.)

Good luck!!

[Teresa Harden]

Thank you Keith for the suggestion, but and I started that route, but I wanted the ‘due by dates’ protected.

[Alexander Bautz]

Hi,
If you still want to do it using mulitiple date fields in your NewForm you can use a code snippet like this:

Add this to your custom js and set up a rule to trigger on change of your “DeployDate” field and have it call the function doCalculateDates.

function dateOffsetDays(date, days){
    var d = new Date(date.valueOf());
    var nDate = d.setDate(d.getDate()+days);
    return new Date(nDate);
}

function doCalculateDates(){
    var deployDate = spjs.utility.getDateFieldAsDateObject("DeployDate");
    // Plus 14 days example
    var plus14 = dateOffsetDays(deployDate, 14);
    spjs.utility.setDateFieldFromDateObject("DateColumn1", plus14);
    // Minus 14 days example
    var minus14 = dateOffsetDays(deployDate, -14);
    spjs.utility.setDateFieldFromDateObject("DateColumn2", minus14);
}

You must change “DeployDate”, “DateColumn1” and “DateColumn2” to match your fields.

If you like to actually prevent them saving your can add this to your custom js:

function dffs_PreSaveAction(){
    if(GetUrlKeyValue("IsDlg") === "1"){
        window.frameElement.commitPopup();
    }else{
        location.href = "The_address_you_want_them_to_go_to";
    }
    return false;
}

Alexander

[Teresa Harden]

Thank you Alexander, this worked PERFECTLY!! I appreciate the immediate response!!

[Author]

Posts