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

[SusanBr]

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

[KathyLe1 · Accepted solution]

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

[NelsonSoans]

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

[SusanBr]

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

[Femke_Crijns]

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

[NelsonSoans]

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!!

[Heather_Kulbacki]

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

[Femke_Crijns]

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 ;-).