Calculating Week Number in calculated field

Question

I'm trying to calculate the week number based on a fiscal year starting on November 1st with moderate success.

Current formula:

=INT(([Project Start Date]-DATE(YEAR([Project Start Date]-WEEKDAY([Project Start Date]-1)+4),11,1)+WEEKDAY(DATE(YEAR([Project Start Date]-WEEKDAY([Project Start Date]-1)+4),11,1))+5)/7)

So the week beginning November 1st 2017 would be marked as WeekNum 1. Week beginning November 8th was marked as WeekNum 2 etc...

As soon as the year changes from 2017 to 2018 the formula begins a reverse countdown from -42 back down to 0 when you get to the week before November 1st 2018 whereas it should be showing as week 10 not -42.

Not sure how to crack this one so any help is greatly appreciated.

Answer 1

I suggest you modifying first “DATE(YEAR([Project Start Date]-WEEKDAY([Project Start Date]-1)+4),11,1) ” into “DATE(2017.11.1)”, then the formula as:

=INT(([Project Start Date]-DATE(2017,11,1)+WEEKDAY(DATE(YEAR([Project Start Date]-WEEKDAY([Project Start Date]-1)+4),11,1))+5)/7)