**Edit: Hi, I know it has been a while since this was answered but I am having some issues with this formula. Since the start of the year it is giving me two different week numbers for each week. This is what my columns look like right now,
ByWeek:2017_01 WeekStart WeekEnd EntryDate 1/6/2017 1/13/2016 1/13/2017 ByWeek:2017_02 WeekStart WeekEnd EntryDate 1/13/2017 1/20/2016 1/20/2017 ByWeek:2017_1 WeekStart WeekEnd EntryDate 1/6/2017 1/13/2016 1/17/2017 ByWeek:2017_2 WeekStart WeekEnd EntryDate 1/13/2017 1/20/2016 1/23/2017
Every WeekNo is being separated into two different weeks, 01 and 1, 02 and 2, etc. and the date ranges are different. Can anyone tell why this would be happening suddenly when this worked fine all last year?
I have a calculated column right now for the purpose of a Weekly Action Report (WAR) that shows entries from a form based on the date the user enters and sorts the entries by week. The issue is our week is not a standard week, it is Wednesday to Wednesday. Here is the formula I have currently:
=YEAR(EntryDate)&"_"&IF(INT((EntryDate-DATE(YEAR(EntryDate) ,1,2-WEEKDAY(DATE(YEAR(EntryDate),1,1))))/7)<10,"0","")&INT((EntryDate-DATE(YEAR(EntryDate) ,1,2-WEEKDAY(DATE(YEAR(EntryDate),1,1))))/7)
This displays results like this: ByWeek: 2016_35 (where 35 would be the 35th week of the year). This works functionally, but my team has to be sure to change the entry date to the previous Friday if they are making an entry for Monday-Wednesday of the current week, else the entry will appear in a new field such as 2016_36, which defeats the purpose of sorting the entries by week for the weekly reports.
Additionally, I would like to display the week range for each week instead of the actual week number if possible. Thanks all.