I'm trying to calculate the week number based on a fiscal year starting on November 1st with moderate success.
=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.