Expand my Community achievements bar.

Week number calculation

Avatar

Level 1
Hi all, I'm trying to build a task report which displays both the task planned completion date as well as a calculated week number for that task's planned completion. This would be equivalent to the WEEKNUM function in Excel. I want to display the week number of the planned completion date-- the tricky part is that some of the tasks are not planned to complete until next year, so I (think I) need to look at the year of the planned completion date, calculate the difference between the task date and Jan 1 of that year, and then calculate the week number from that difference. Would be great if there was a DAYOFYEAR function! Can anyone help with a workaround? Surely someone has done this before?
Topics

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

9 Replies

Avatar

Community Advisor
Hi Jody. The closest helper I am aware of is the DAYOFMONTH function, but I'm not sure that helps much. To roll your own DAYOFYEAR, perhaps you could DATEDIFF the date in question against Jan 1 of that date's Year -- something like DATEDIFF(Planned Start Date,DATE(CONCAT(YEAR("0101",Planned Start Date)))). I've not tested it, mind you, but thought it might give you some ideas. Regards, Doug

Avatar

Level 8
Might be an initial pain to setup, but you can do a calculated field on a task form like this: IF(Planned Completion Date < DATE("01/08/2017"), "1", IF(Planned Completion Date < DATE("01/15/2017"), "2", IF(Planned Completion Date < DATE("01/22/2017"), "3", IF(Planned Completion Date < DATE("01/29/2017"), "4" )))) I tried getting greater than > to work, but it gave the wrong results, but the less than worked great.

Avatar

Level 6
Hi, I unfortunately don't have a solution for you. But I am looking to try and do the same thing, but I want number of weeks from a date set in a custom field. Did you by chance ever figure anything out? Thanks! Mollie Shatek JLL

Avatar

Level 6
Hi, Just thought I'd share. I think I got a solution to display the Week number a task starts from a Project custom field. CONCAT("Week ",ROUND(((DATEDIFF(Planned Start Date,Project.Contract Date))/7),0)) You could try something like: CONCAT("Week ",ROUND(((DATEDIFF(Planned Completion Date,Project.Planned Start Date))/7),0)) If you want more exact you could take of the ROUND function. Hope that helps! Mollie Mollie Shatek JLL

Avatar

Community Advisor
Hi Mollie, The solution for the original post is this: ROUND((((DATEDIFF(Planned Completion Date,DATE(CONCAT("1/1/",YEAR(Planned Completion Date))))+1)-(8-DAYOFWEEK(DATE(CONCAT("1/1/",YEAR(Planned Completion Date)))))-DAYOFWEEK(Planned Completion Date))/7)+2,0) I think your solution is not as complicated: CEIL(DATEDIFF(Planned Completion Date,{DE:Custom Field Name})/7) That is the difference in number of days between your custom field date and the task planned completion; divided by 7; then rounded up to the nearest integer. Also, you could calculate the number of whole weeks between a task's planned completion and a custom date on a project form: CEIL(DATEDIFF(Planned Completion Date,Project.{DE:Custom Field Name})/7) William English
If you like my content, please take a moment to view and vote on my Idea Requests: https://tinyurl.com/4rbpr7hf

Avatar

Level 6

To get the actual ISO Week Number the formula is:

valueexpression=IFIN(DAYOFWEEK(CONCAT("1/1/",YEAR({entryDate}))),1,6,7,CEIL(DIV(DATEDIFF({entryDate},CLEARTIME(CONCAT("1/1/",YEAR({entryDate})))),7)),SUB(CEIL(DIV(DATEDIFF({entryDate}),CLEARTIME(CONCAT("1/1/",YEAR({entryDate}))),7)),1))
valueformat=HTML

 

This accounts for Workfront using Sunday as Day 1 of the week.

 

I used entryDate as my date but you could use whatever date field you wanted in place of this one.

Avatar

Level 2

Hello @GrahamJarrett - I tried your expression but I am getting errors. It tells me that the second DATEDIFF is incorrect because the function must contain 2 values. Please advise. Thanks! 

SeannaHe_0-1709817948297.png

 

Avatar

Level 6

I actually have updated my formula too...

 

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

Avatar

Level 2

Excellent @GrahamJarrett! My use case replaces {entryDate} with the wildcard $$TODAY and it works perfectly!

 

Thank you so much!