Make Due Date Last Day of the Month

Very good idea; By extension, the last day of the week and the last day of the year would be good to have as well. The same exists in Outlook Calendar and I'm using it frequently for recurring deadlines/reminders of tasks that fall at the end of a week or month, independently of the actual numerical date.

Hello Doug and Chris,

I have come up with a formula that sets the duration so, that the due date gets set to be the last day of the month that the work item starts on. To implement this:

1) Create a workflow rule that on a Work Item

2) Set Run Time >> Only when a record is created

3) Set any additional Evaluation Criteria if you like (e.g. Contains($Name, "test") would only run this rule on Work Items with names containing the word "test")

4) Set Actions >> Update field >> Choose field Duration

5) Use the following code in the field provided:

Days(1 +
      DateDiff(
            Date(
                  ToNumber(ToString(Year($StartDate))),
                  ToNumber(ToString(Month($StartDate))),
                  Case(
                        ToNumber(ToString(Month($StartDate))),
                        1, 31,
                        2, Case(Mod(ToNumber(ToString(Year($StartDate))),4), 0, 29, 28),
                        3, 31,
                        4, 30,
                        5, 31,
                        6, 30,
                        7, 31,
                        8, 31,
                        9, 30,
                        10, 31,
                        11, 30,
                        12, 31, 999
                  )
            ),
           $StartDate,
           "d"
      )
)

Please let me know if his is helpful.

Hi Roland,

Thanks so much! I tried to tack this on to an existing Scheduled Work Flow we use to automatically create a Milestone each month from a Template (with Tasks underneath it), from which I created a Custom Action to test it. When I launched the CA, I got the following error:

"Hammock's 'Duration' field cannot be changed since it is calculated from all items below."

The template being used for this is pretty simple: One Milestone with one Task underneath it (see attached screenshot). I had the Constraint Type for the sub-Task set to ALAP, so I tried changing it back to ASAP. I also had hard-coded Work and Duration values on the Task, so I tried removing hose as well with no luck. Any ideas?

I feel like we are close thanks to your code! This would be HUGE for us if we can get it to work. Thanks again!

I've been playing with this all morning. It does work if I setup the WFR to only run for a single ad hoc Task. But when I try to apply it to Tasks created from Templates, the behavior is inconsistent. I have tried simply having it run every time a Task is created, and I've also created a Custom Field (toggle) called "Due Last Day of Month," and modified the WFR to only run if that field is "True." Neither one of those is producing the results I'm looking for.

In the latter scenario, it worked for a Project I created from a Template with a start date of 4/3/17 (the Tasks had a due date of 4/30/17). But when I created a Project with a start date of 5/1/17, the Task due dates were 5/8/17. I tried toggling the "Due Last Day of Month" field to "True" only for the Tasks, only for the Tasks and the parent Milestone, and for all Work Items in the Template (Project, Milestone, and Tasks under the Milestone). None of those combinations seem to be working correctly.

Can you think of another method I can use to force the Tasks that are created from my Template to run this WFR/formula? Ultimately, it is only the "leaf" Tasks that must have the due date set to the last day of the month, so if I can accomplish that, I think we'd be good to go.

Thanks again!

Hello Doug,

I just created a workflow rule on a task, containing the same exact formula. Then I tested it using a template with multiple milestones, and tasks, starting on 5/1/17 and to my understanding the rule worked just fine. Please see the recording of the test: https://www.screencast.com/t/j5n6lkXFtDF9

Is that the behaviour you are expecting? Please also note, that this rule wont work well when there are dependencies set.

Please let me know if this is helpful. 

Hi Roland,

That does certainly look correct. I am not using any dependencies in my templates. I guess it must be something in my template that is causing the seemingly "random" behavior, though. To be clear, did you create the workflow rule at the task level, or the work item level?

I will try building some templates from scratch rather than trying to work with our existing templates to see if I have better luck.

Thanks!

Hello Doug, 

I created it on a Task level, to prevent the aforementioned "Hammock's 'Duration' field cannot be changed since it is calculated from all items below." error.

Roland,

Are you using any evaluation criteria for your tests? Or does your workflow rule run for every new task that's created? I'm just wondering if you had to "hard code" anything in your template to make sure the WFR is triggered.

Hi Doug,

Because I don't want the rule to run every single time, it only runs when this is true: Contains($Name, "Some_phrase"). Some_phrase can be replaced by any string that would trigger the rule if the name of the task contains that phrase.

Roland,

I just created a new project with a milestone containing two sub-tasks, and then created a template based on that project. When I created a project based on that new template and set the start date as May 1, the due dates were indeed set as May 31 as expected (and as demonstrated in your recording). But when I tried creating one with a start date of April 3, June 1, or July 3, the dates were nowhere close to the last day of the month (they were April 22, June 8, and July 18). At the moment, my WFR runs your formula for every new task that is created for testing purposes.

Is it working for you if you create a project from your template in April, June, or July?

Hello Doug, 

I have tested this rule for all months, different years, leap years in February and the rule is working just fine. Maybe the problem is that you have no evaluation criteria. Try using the same one that I'm using:

This makes the rule run only when the task is created from a template and when the task's name contains the string "end".

Please let me know if this helps.

I still can't get it to work. So strange...I have duplicated your WFR evaulation criteria:

I took out the "IsFromTemplate()" as a test as well, and when I create a single task with "end" in the name, it does indeed work. But when it comes from a template, I still get odd results. Here is a test project I created from a template with a start date of June 1, with two tasks (both containing "end"):

Not sure why this isn't working for me. We do have our default work policy set to "fixed work," so I tried changing it back to "fixed duration," but that didn't make a difference...

I think I may have figured it out...When I created the project I was using to create my template, I was changing the start date to be the first of a month (like March 1 or April 1). If I just leave the start date as today, and then create the template, it seems to work! I'll keep testing, but it may have been as simple as that. Thank you again, Roland!

The issue definitely could be related to that, because my formula relies heavily on the start date. 

Roland,

Thanks again for your continued help on this. We are so close! Another question: one of our primary project templates creates a milestone called "Setup" that contains three sub-tasks called "Month One," "Month Two," and "Month Three." Those three tasks need to start on the first day of three consecutive months, and be due on the last day of each of those months. So if we have a "Setup" milestone that starts on April 1, the "Month One" task needs to start on April 1 and be due on April 30. "Month Two" needs to start on May 1 and be due on May 31. "Month Three" needs to start on June 1 and be due on June 30.

Can you think of a way to accomplish that, leveraging your formula (or a modified version of it)?

Thanks again!

Hi Doug, I would recommend going through our PS department since this rule is going to get exponentially more complex and will impact performance.

Josh,

Impact performance? I don't like the sound of that... At any rate, I am working with our CSM who will engage the PS department to assist with this. Thanks.

Josh/Roland: I think I'm really close to getting this to work. 

Last night, I ran a successful test and the due dates were set as expected, ONE TIME. After that, I tried creating several more Projects using that same Template, and the due dates for "Month One" were always 4/30 as expected, but the Month Two/Three due dates were not being set correctly (they were set to 5/28 and 6/28). Before I went to bed, I created one more Project, and the due dates were wrong. When I came in this morning, they were correct!

That brings me to my question: Is there ever a delay in calculating things like due dates when there is a formula like that involved? I do notice that every time I create a Project using that Template, there's an '!' next to the Project briefcase icon, which indicates that a recalculation is required. Usually, that goes away fairly quickly if I keep refreshing. Just curious if this is a possibility, or if something else is going on (or if I'm just losing my mind). Thank you!

Yes, there can indeed be a delay on either large projects or complex configurations. It is completely normal for it to take several minutes to calculate for rules like this.

Josh,

In this case, I'm pretty sure it took several HOURS...

How many lines was the project and what time did you run it at?  The rule could have taken so long to run that it got "queued" on our job servers, which means it will run when there is low demand on those servers.

The Project Template creates three Milestones. The first two have two Milestones, and the third has one Milestone. I ran it around 9:00PM PST last night. I created two more Projects using the same Template at about 8:00AM PST this morning, and they still are showing incorrect due dates for the second two Milestones (the first one is always correct). Maybe it's something in my WFRs or Templates. I do have a ticket open with support now, so I'll let them take it from here. Thank you!

Perfect, going into your account would be the next step =)