Expand my Community achievements bar.

SOLVED

Is there a way to determine the week number of a date in WorkFront

Avatar

Level 2
I am trying to generate a report that includes the planned start date of a task, but also includes the Week Number that corresponds to the Planned Start Date of the task. In Excel, I can use the WEEKNUM function. Is there a way to do this in a WorkFront report column? Thanks! Susan Browning Internet Testing Systems
Topics

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

1 Accepted Solution

Avatar

Correct answer by
Level 3
Hi Susan, This isn't the least bit elegant, and the performance would probably be unpleasant in a large report, but if you're desperate, I believe the following will work. In a quick test I did where I exported a decent subset of data and compared the results against those of WEEKNUM, everything matched. displayname=Test textmode=true valueexpression=ROUND((((DATEDIFF({plannedStartDate},DATE(CONCAT("1/1/",YEAR({plannedStartDate}))))+1)-(8-DAYOFWEEK(DATE(CONCAT("1/1/",YEAR({plannedStartDate})))))-DAYOFWEEK({plannedStartDate}))/7)+2,0) valueformat=HTML Kathy

View solution in original post

7 Replies

Avatar

Correct answer by
Level 3
Hi Susan, This isn't the least bit elegant, and the performance would probably be unpleasant in a large report, but if you're desperate, I believe the following will work. In a quick test I did where I exported a decent subset of data and compared the results against those of WEEKNUM, everything matched. displayname=Test textmode=true valueexpression=ROUND((((DATEDIFF({plannedStartDate},DATE(CONCAT("1/1/",YEAR({plannedStartDate}))))+1)-(8-DAYOFWEEK(DATE(CONCAT("1/1/",YEAR({plannedStartDate})))))-DAYOFWEEK({plannedStartDate}))/7)+2,0) valueformat=HTML Kathy

Avatar

Level 3

This worked for me like a charm @Kathy Leeman‚, and, it was elegant :)

Avatar

Level 2
Kathy, Thank you so much, this works! Performance is not a huge issue. I'm not running this frequently. Susan Susan Browning Internet Testing Systems

Avatar

Level 4

Hi @Kathy Leeman‚ and @Nelson Soans‚ ,

I was excited to find a text mode calculation to show the week number.

Unfortunately, when I paste this code into a column in a task report or view it only returns "1" or "0".

Any suggestions for what I do wrong here?

displayname=Test

textmode=true

valueexpression=ROUND((((DATEDIFF({plannedStartDate},DATE(CONCAT("1/1/",YEAR({plannedStartDate}))))+1)-(8-DAYOFWEEK(DATE(CONCAT("1/1/",YEAR({plannedStartDate})))))-DAYOFWEEK({plannedStartDate}))/7)+2,0)

valueformat=HTML

I'm still working on my text mode skills, but this one is too complex for me...

Kind regards,

Femke

Avatar

Level 3

hey @Femke Crijns‚ , your formula is exactly the same as what I used in my report based on Kathy' suggestions. It worked like a charm for me but not sure why you are seeing the 1s and 0s!!

Avatar

Community Advisor

This worked great for me - or so I thought...

i was trying to get the FISCAL week of the year, with the fiscal year starting on 1/3

worked great for US formatted dates, but when the user has their browser set to another language that formats the date as DD/MM/YY, it reads "1/3/" with 3 as the month and 1 as the day and flubs the formula. I changed it back to 1/1 and used some different math to account for the first week of the year date shift.

just wanted to through that FYI out here

Avatar

Level 4

Hi all,

 

I'm still struggling with the week numbers and looking for some help.

 

I see the week numbers now, but the week starts on Sunday in this formula. I have tried several changes in the formula, but I can't find the value to change to start a new week on Monday.

And in 2022, the week numbers are correct (except for Mondays), but in 2023 it counts one week less than it should be.

23 September 2023 is week 38 instead of week 37.

 

Hopefully, someone with great textmode skills could help me out here ;-).