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.

1 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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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