Has anyone created a custom column or field that calculated the a specific day after a planned completion date on a task?

[BrittanyB88]

I am not sure if this is possible. But we have a meeting that is every Tuesday for business case approvals. I have tasks for our teams to finalize the business cases to be presented at the meeting. I currently have a report that pulls that task so we can see how many we have a month, but I would like a column that shows which Tuesday meeting date it will be in after that planned completion date.

[Richard_Le_]

Hi Brittany,

Not sure if i'm understanding correctly . . . . Is your Tuesday meeting date always the next Tuesday after the planned completion date? Or could it be any Tuesday later in the month?

If it is always the next Tuesday after the planned completion date, you could create a calculated column in your report that will dynamically work out what date the very next Tuesday will be after planned completion. Let me know if this is the case as I have some code I can share that will do this.

If it could be any Tuesday after planned completion, you would need to create a custom form with a date field on it that can be attached to these tasks. This date field could then be added to your report view, where you would be able to manually populate the date of your business case meetings.

Best Regards,

Rich.

[BrittanyB88]

Yes, it is always the next Tuesday after the planned completion. I would love a the code! Thank you!

[Richard_Le_]

No problem Brittany. Here you go:

displayname=Next Tuesday

textmode=true

valueexpression=IF(DAYOFWEEK({plannedCompletionDate})=1,ADDDAYS({plannedCompletionDate},2),IF(DAYOFWEEK({plannedCompletionDate})=2,ADDDAYS({plannedCompletionDate},1),IF(DAYOFWEEK({plannedCompletionDate})=3,ADDDAYS({plannedCompletionDate},7),IF(DAYOFWEEK({plannedCompletionDate})=4,ADDDAYS({plannedCompletionDate},6),IF(DAYOFWEEK({plannedCompletionDate})=5,ADDDAYS({plannedCompletionDate},5),IF(DAYOFWEEK({plannedCompletionDate})=6,ADDDAYS({plannedCompletionDate},4),IF(DAYOFWEEK({plannedCompletionDate})=7,ADDDAYS({plannedCompletionDate},3))))))))

valueformat=HTML

What this expression is effectively doing is working out what day of the week the planned completion date falls on (1 being a Sunday, 2 being a Monday etc.). It is then adding the appropriate number of days to the planned completion date to ensure that the returned date is always next Tuesday.

So, if your planned completion date was a Monday, the day of week would be 2 and we would add 1 day to this date to get the next Tuesday. If your planned completion date was a Tuesday the day of week would be 3 and we would add 7 days to get the next Tuesday, and so on.

Hope that helps!

Best Regards,

Rich.

[BrittanyB88]

There seems to be some error. I have changed some of the text to add more days. The days are all all work days, would that change the calculations

[Richard_Le_]

Hi Brittany,

It looks like something has gone wrong in the code when you changed it. In my environment using the original code, it works perfectly:

The code doesn't look at work days, just days in general. It should automatically work out what the next Tuesday is regardless of any non working days.

Why are you wanting to add more days? If you could explain what you're trying to achieve, I'd be happy to try an help.

Best Regards,

Rich.

[Kundanism]

Hi @Richard Leek‚

Thank you for sharing the above code, it works great if the condition is for Tuesday. What I should change if it is on Monday. Sorry to ask about, I am not very expert in text code. 😊

Mvh

kundan.

[Richard_Le_]

Hi Kundan,

No problem at all. It's just a case of updating the math, if we are now working out a Monday we need to add one less day since Monday is one day before Tuesday. So the code would look like this:

displayname=Next Monday

textmode=true

valueexpression=IF(DAYOFWEEK({plannedCompletionDate})=1,ADDDAYS({plannedCompletionDate},1),IF(DAYOFWEEK({plannedCompletionDate})=2,ADDDAYS({plannedCompletionDate},7),IF(DAYOFWEEK({plannedCompletionDate})=3,ADDDAYS({plannedCompletionDate},6),IF(DAYOFWEEK({plannedCompletionDate})=4,ADDDAYS({plannedCompletionDate},5),IF(DAYOFWEEK({plannedCompletionDate})=5,ADDDAYS({plannedCompletionDate},4),IF(DAYOFWEEK({plannedCompletionDate})=6,ADDDAYS({plannedCompletionDate},3),IF(DAYOFWEEK({plannedCompletionDate})=7,ADDDAYS({plannedCompletionDate},2))))))))

valueformat=HTML

Best Regards,

Rich.

[BrittanyB88]

What you are seeing is your text that you gave me, not anything I changed. It autos to the Monday, minus one or two of them which have Tuesday. I had tried to change it to look at different days since it's looking at Monday right now. That's why I brought it up. I am not sure why your text is not showing up as Tuesday as it should on my end.

[Richard_Le_]

Hi Brittany,

That's bizarre. Strangely it's not a consistent issue either. In your screen shot you have a Wednesday planned completion date (8th Dec) that returns a Monday (13th Dec) in the 'next Tuesday' variable, which is incorrect. . . . . But you also have a Wednesday planned completion date (19th Jan) that does return a Tuesday (25th Jan), which is correct. I can't replicate that in my environment, i've tried using different schedules, assigning diffferent users, adding public holidays etc., but I always get a Tuesday.

Silly question, but I have to ask. Are you definitley referencing the native planned completion date attribute in the planned completion date column on your report? There's no way that this is using a date attribute that is different from the native plannedCompletionDate attribute (possibly a custom field that someone named as planned completion date)?

Best Regards,

Rich.

[BrittanyB88]

Sorry, I was out of office. It is the native planned completion date and not a custom field. I did double check that. It is very bizarre in the fact that it is coming up differently but in a random way as in there is no consistency to it. I am going to try it on a different task. Because this is looking at three differently named tasks in the filters.

[BrittanyB88]

We figured it out. The system was considering a task planned completion after 5pm as the next day and so the added days were off by 1. Once we moved it to be before 5pm, it worked perfectly.

Thank you again for the calculation!

[MichaelPe1]

Hello, I had a user today ask me if there was a way in a project timeline to make a task default to a Thursday start date. I thought of this thread but I am not sure if it will apply. Basically it's a task for a submission but the submissions always have to happen on a Thursday. So no matter what the predecessor is we would need to make sure that task calculates to the next Thursday. Is there a way to accomplish this?

[Richard_Le_]

Hi Tracy,

It's not possible to have the start date default to a specific day of the week using native Workfront functionality, but this could probably be achieved using Fusion.

An approach you could take (which I believe is what Brittany does) is create a report that pulls in all submission tasks, and then create a calculated column that displays when the next Thursday would be.

Best Regards,

Rich.

[Doug_Den_Hoed__AtAppStore]

Huh...actually, @Michael Perez‚ and @Richard Leek‚, after some experimenting today, here is . . .

How to Schedule a certain Task (e.g. "Submission") to occur on a particular Weekday (e.g. "Thursday")

- By assigning a Task to a (fake) User tied to a particular Schedule (e.g. "Thursdays 9-5")

- When there isn't enough time to START that task on a particular Thursday (e.g. 1 hour)

- Project > Recalculate Timeline will delay it until the next available window (e.g. Next Thursday)

- Noting that if there is ANY time at all available to Start such a task (e.g. 6 minutes on Thursday)...

- Project > Recalculate Timeline will START it, but "stretch" the duration until there is time on the Schedule to FINISH it (e.g. Next Thursday)

BONUS TIPS: consider setting up such Tasks using Templates, using our Sync Template solution to update such an Assignee across existing Template-based Projects, and using our Force Timeline Recalculation solution to ensure the dates are updated (e.g. nightly)

Thanks for the cool thought exercise!

Regards,

Doug