In Week brief calculated field on a form

[DanIn1]

Hi is there a formula that tells me if job was entered into Workfront the same week that it is due

In essence

If Issue ENTRY date is this week & Project Planned completion date is this week it's an in week brief because its wanted the same week it was briefed in

Any help would be much appreciated

Dan

[SamanthaIsin]

You could try an IF statement in a custom-calculated field!

This is close: IF(Entry Date=$$TODAY&&Planned Completion Date=$$TODAY,"In Week Brief","Out of Week Brief")

The only problem I'm running into is how to indicate that you don't just want the entry date and the completion date to be today, you want them to be this week. The wildcard variables for date and time are only for today or now. If you wanted to do this week you would have to do something like this:

$$TODAYbw between $$TODAYew

That would work, but I don't know how to put that in a calculated field with the right syntax. When I just tried $$TODAYw it didn't give me an error, but it didn't give me an answer either. @Skye Hansen‚ Do you know‚ how would you do that?

Also, here's some documentation that may help in the future.

Understanding and Building IF statements: https://one.workfront.com/s/document-item?bundleId=the-new-workfront-experience&topicId=Content%2FRe...

Understanding Condition Operators: https://one.workfront.com/s/document-item?bundleId=the-new-workfront-experience&topicId=Content%2FRe...

Understanding WIldcard Variables: https://one.workfront.com/s/document-item?bundleId=workfront-classic&topicId=Content%2FReports_and_D...

[AnthonyIm]

Hi - so I figured out the formula, but there is one question.... when you say "Project Planned Completion Date" what do you mean? Are you looking at a custom field on when they want the project done? Are you looking at the project the Issue itself is on? Or are you looking at the Planned Completion Date of the project you created by converting the issue into a project?

In my formula, I'm looking at the Planned Completion Date of the project that the issue itself is on, but let me know and I can update it for you.

IF(CLEARTIME(ADDDAYS(Project.Planned Completion Date,(1-DAYOFWEEK(Project.Planned Completion Date))))=CLEARTIME(ADDDAYS(Entry Date,(1-DAYOFWEEK(Entry Date)))),"In Week","All Good")

NOTE: Because this is a calculated field on the issue, it only updates when the issue is edited. So if the Project's Planned Completion Date changes, it won't reflect that right away. My recommendation... when you run this report, select all the issues, bulk edit, go to Recalculate Custom Expression, and save. THEN use that data to export or share with people.

How I got here...

Wanted to get a commonality for the two dates. Decided I would go with the Sunday of their week. Because you can't just do entryDatebw (like you could we $$TODAYbw), I had to work with numbers and DAYOFWEEK. I took 1 minus the day of the week for the Entry Date to get the difference, which give you a negative number. I then did ADDDAYS with the negative number to go backwards in time and give me the Sunday of the week that the Entry Date was on. (And then did the same for the Project's Planned Completion Date)

• Entry Date of my test issue was 5/27
• Day of Week of entry date is 5
• 1 minus 5 is -4
• If I add -4 to the Entry Date, I get 5/23
• Project's Planned Completion Date is 5/28
• Day of the Week of PCD is 6
• 1 minus 6 is -5
• If I add -5 to the PCD, I get 5/23

Once I had some commonality (Sundays), I then did my IF statement to compare the two dates. If they were the same, then it was "In Week". If they were different, it was "All Good".

NOTE: I had to add "CLEARTIME" as I found the time of day was messing up the equals condition in the IF statement.

Hope that helps everyone. It was a fun challenge!

[SamanthaIsin]

Whoa. @Anthony Imgrund‚ you are the ultimate Workfront Master.