Expand my Community achievements bar.

Don't miss the Workfront Community Lens, June Edition!

Planning - Date Difference calculation formula field

Avatar

Level 3

Hi Community,

 

I have a question about a time difference calculation in Planning. I have a field called End Date and i want to calculate the weeks until this End Date in T-weeks.

 

I used this formula with the intend to receive the following results: T-6, T-4, T-0 etc., but for some reason the $$TODAY makes issues in the formula field for Planning, since I am either getting T-0 for all calculations/data variations or no results at all.


Any thoughts? Workarounds? Tipps?

I already tried the following calculations:

IF({Date End}>$$TODAY,
CONCAT("T-",ROUND(DATEDIFF($$TODAY,{Date End})/7)),
"T-0")
 
IF({Date End}>$$TODAY,
   CONCAT("T-",ROUND(DATEDIFF($$TODAY,{Date End})/7)))
 

CONCAT("T-",ROUND((DATEDIFF($$TODAY,{Date End}))*-1/7))

DATEDIFF({Date End},$$TODAY)

ROUND(DATEDIFF({Date End},$$TODAY)/7)

CONCAT("T-",ABS(ROUND(DATEDIFF($$TODAY,{Date End})/7)))

WEEKDAYDIFF($$TODAY,{Date End})

 

Thank you for any thoughts in advance.

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

8 Replies

Avatar

Level 4

Haven't tested any of these calcs but the first thing that caught my eyes was that you're missing the "DE:" in your calculations. Every instance of the Date End field should appear as follows:  {DE:Date End}

Avatar

Level 3

No planning is not using the "DE:" at least not for the calculations i have done so far. But thanks for the thought!

Avatar

Community Advisor

Still wondering if the field name is End Date or Date End.

Avatar

Level 3

Sorry, you are absolutely right, that was confusion. It is "Date End".

Avatar

Community Advisor

Taking a look at the Planning documentation, it doesn't say you cannot use wildcards. So I'd recommend you submit a case to Workfront Support or have your authorized contact submit a case. It can be as simple as this:

 

Take the simplest calculation you can, for example, CONCAT($$TODAY) is pretty foolproof and likely to produce a result, but instead it only displays the first part, and not the wildcard part. Then point out to support that it's not throwing an error, and that documentation doesn't indicate it shouldn't be used. Then, ask what the intended result should be.

Avatar

Community Advisor

Adding that I was able to get a bit of time in Planning just now, and it looks like $$NOW is displaying. Either using $$NOW, or CLEARTIME($$NOW) might be a good workaround until you figure out what's going on with $$TODAY

Avatar

Level 3

Oh great! Thank you for testing and I also submitted a support ticket in parallel I usually do when asking a pressing question on here as well. Thank you so much!

 

Avatar

Level 3

Unfortunately after replacing $$TODAY with $$NOW the T-0 disappears as it did before for some of the examples. No results, just blank, but no error message for the formula. Same for CLEARTIME($$NOW)