0

I have several custom date formulas setup which all worked fine in 2016. Now that 2017 is here it seems there is some miscalculation happening within the formula, but I cannot tell what the problem is. The page is a weekly action report,the dates are supposed to start each week on Tuesday and end on Tuesday of the next week. Here is the code I have currently.

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

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

WeekStartDate
=DATE(YEAR(EntryDate),1,1)+INT(RIGHT(ByWeek,LEN(ByWeek)-INT(FIND("_",ByWeek))))*7-2

WeekEndDate
=WeekStartDate+7

Those are my main formulas. The issue is this is the output I am getting currently.

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

So as you can see, my week day appears to be starting on Friday instead of Tuesday, and I am getting two different ByWeek filters for each WeekNumber. Any help you guys can give would be greatly appreciated.

0

I haven't tried to understand all of it..
but it looks like you are doing something with Strings and then doing Calculations on them.

SharePoint is very forgiving with types.. but I'd say that is where you go wrong.

I would start with a proper formula for WeekStartDate without using Strings and using the WEEKDAY of EntryDate

The WEEKDAY function takes a second parameter

source: http://www.viewmaster365.com/365coach/#/Calculated_Column_Functions_List

  • The EntryDate variable is a date the user enters with a date picker, and the WeekStartDate formula isn't critical to the success of this form, it's really more informational than anything else. The WeekNumber formula is the essential formula that everything else is based on. It is supposed to tell me which week the EntryDate is in and group the entries based on the Tuesday to Tuesday week. The WeekNumber formula was created with help from other users on StackExchange, so if you think it is poorly made I would greatly appreciate your help in re-creating a better version. Thanks for your answer. – Tydis Jan 18 '17 at 12:34
  • What does the last example in the screenshot show? – Danny '365CSI' Engelman Jan 18 '17 at 13:08

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.