Week number calculation

[JodyKo]

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?

[Doug_Den_Hoed__AtAppStore]

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

[AdinaPi1]

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.

[MollieSh]

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

[MollieSh]

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

[William--]

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

[GrahamJarrett]

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.

[Seanna_Hdz]

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! 

[GrahamJarrett]

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)

[Seanna_Hdz]

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

Thank you so much!