Is there a way to determine the week number of a date in WorkFront | Community
Skip to main content
S
SusanBr
Level 2
April 25, 2018
Solved

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

  • April 25, 2018
  • 5 replies
  • 2014 views
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
This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by KathyLe1
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

5 replies

K
KathyLe1Accepted solution
Level 4
April 26, 2018
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
N
nsoans1106
Level 2
April 20, 2021

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

S
SusanBrAuthor
Level 2
April 26, 2018
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
F
FemkeCr
Level 4
September 2, 2021

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

N
nsoans1106
Level 2
September 27, 2021

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
    Community Advisor
    Heather_KulbackiCommunity Advisor
    Community Advisor
    October 28, 2021

    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

    F
    FemkeCr
    Level 4
    October 14, 2022

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

    Terms & ConditionsAccessibility statement

    Loading footer...