Custom Calculated Column to sort dates based on entry date

Question

**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.

Answer 1

Create another calculated column as WeekNo just to increase the readability of the final formula

WeekNo=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)

Then, if the EntryDate is Sunday, Monday or Tuesday or Wednesday actual week number should be WeekNo-1

=IF(WEEKDAY(EntryDate)=1,YEAR(EntryDate)&"_"&WeekNo-1,IF(WEEKDAY(EntryDate)=2,YEAR(EntryDate)&"_"&WeekNo-1,IF(WEEKDAY(EntryDate)=3,YEAR(EntryDate)&"_"&WeekNo-1,IF(WEEKDAY(EntryDate)=4,YEAR(EntryDate)&"_"&WeekNo-1,YEAR(EntryDate)&"_"&WeekNo))))

Answer 2

It appears that the formula for the WeekNo you provided in the question has some issue. All other formulas are depending on this formula. I have changed the formulas to resolve the issue. Please try below formulas.

WeekNo =INT((EntryDate-DATE(YEAR(EntryDate),1,1)+7-WEEKDAY(EntryDate,1))/7)+1

WeekEnd =DATE(YEAR(EntryDate),1,1)+WeekNo*7-1

WeekStart =WeekEnd-6

ByWeek =IF(AND(WeekNo=1,WEEKDAY(EntryDate)<5),YEAR(EntryDate)-1&"_"&INT((EntryDate-INT(WEEKDAY(EntryDate))-DATE(YEAR(EntryDate-INT(WEEKDAY(EntryDate))),1,1)+7-WEEKDAY(EntryDate-INT(WEEKDAY(EntryDate)),1))/7)+1,IF(WEEKDAY(EntryDate)<5,YEAR(EntryDate)&"_"&WeekNo-1,YEAR(EntryDate)&"_"&WeekNo))

I have assumed week number 1 is starting from January 1st of each year.