3

I'm currently using two lists, one stores tasks [Task List], the other stores the hours worked for completed tasks [Time Sheet List]

When a user marks a task as complete, it creates a corresponding Time sheet entry in the Time Sheet list.

I'm currently trying to group time sheet entries so that I can calculate the total hours an employee has worked that week.

I've tried creating a calculated column that assigns the week number based on the date, then group by that week number but the syntax is proving difficult.

Any help is greatly appreciated.

Improve this question
1
  • Some further info: The field i'm using is the Start Date, the year starts November 1st. So the current calculation should return week 6.
    – cod3r_jim
    Commented Dec 7, 2017 at 10:11

1 Answer 1

Reset to default
2

The following syntax will get the week number based on the created column, you'd need to modify the column with your own. (This is based on European week numbers instead of American)

=INT(([created]-DATE(YEAR([created]-WEEKDAY([created]-1)+4),1,3)+WEEKDAY(DATE(YEAR([created]-WEEKDAY([created]-1)+4),1,3))+5)/7)

This blog gives a good break down of how the syntax is created (quote below to preserve in case the blog goes down:

https://joranmarkx.wordpress.com/2011/12/09/use-the-calculated-field-to-show-weeknumber-in-sharepoint-2010/

Calculate the distance between the beginning of the year (with regard of the start of week 1) to the datecolumn

Add the day of the week of the 1st January of the datecolumn year added with 5 (Thursday)

Divide by number of days in a week (usually 7 😉

Floor by doing a cast to INT

Improve this answer
8
  • The syntax doesn't seem to work, unsure as to why. I've replaced the column name with my column name so there shouldn't be any issues. This solution is for SP2010 so it could be outdated. My Syntax: =INT(([Start Date]-DATE(YEAR([Start Date]-WEEKDAY([Start Date]-1)+4),1,3)+WEEKDAY(DATE(YEAR([Start Date]-WEEKDAY(Start Date-1)+4),1,3))+5)/7)
    – cod3r_jim
    Commented Dec 7, 2017 at 10:01
  • Your last start date doesn't have [] around it, could you update to [Start Date] and try again please?
    – Jason Clair
    Commented Dec 7, 2017 at 12:40
  • Apologies, that has sorted it out! I had just copied the snippet you had posted, it had brackets missing on it to. Final point, this is based on January being the first week of the year, how do i set it to make November start as the first week?
    – cod3r_jim
    Commented Dec 7, 2017 at 17:27
  • Whoops, I'll update the original post with the updated brackets. I've done a quick test, and to update it to use November you should just need to update the bits which say "1,3" to say "11,1" (Please note, I've not had a chance to test it fully so I'd recommend trying with a few different dates to make sure you get expected results)
    – Jason Clair
    Commented Dec 7, 2017 at 20:19
  • 1
    Spot on once again @Qurator . Found another way to do it as well which is: =INT(([Actual Start]-DATE(2017,11,1)+WEEKDAY(DATE(YEAR([Actual Start]-WEEKDAY([Actual Start]-1)+4),11,1))+5)/7)
    – cod3r_jim
    Commented Jan 11, 2018 at 9:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.