0

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

0

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))))
  • where should I enter the 2nd formula, in the WeekNo column or the ByWeek column I have? – Tydis Sep 28 '16 at 17:04
  • In the ByWeek column. – Prasanna Sep 28 '16 at 17:07
  • Ok, I added the WeekNo column, which works fine. I added an entry with yesterday's date and it appeared in the current reporting week which is exactly what I needed. Only thing left would be to make the reporting date more readable if that's possible. – Tydis Sep 28 '16 at 17:12
  • Assume you need to find Week Start Date and End Date based on the entry date and week starts on Wednesday Create 2 Date Only calculated columns as WeekStartDate and WeekEndDate. Then formulas would be WeekStartDate =DATE(YEAR(EntryDate),1,1)+INT(RIGHT(ByWeek,LEN(ByWeek)-INT(FIND("_",ByWeek))))*7-2 WeekEndDate =WeekStartDate+7 – Prasanna Sep 28 '16 at 18:17
  • Hi, I know it has been a while since this was answered but I am having some issues with this formula and I hope you can help. 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 – Tydis Feb 1 '17 at 9:43
0

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.

  • The ByWeek formula is giving me a syntax error, but the other formulas went in fine. The other thing is our reporting period is from Tuesday to Tuesday, rather than Monday-Friday. Thanks for all your help! – Tydis Feb 16 '17 at 11:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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