I am creating a gantt chart in excel for my plan. My formula determines the start week number and end week number, and displays "1" in a cell corresponding for each week number in a timeline. My challenge is how to use this logic when my fiscal year starts in April 2018 and ends in April 2019. If my timeline starts, say in week 46 of 2016 and ends in week 10 of 2019, my formula does not work. Here is the formula I am using:
=AND(K$7>=$H9)*(K$7<=$I9)*1
where K7 is my week number in a timeline, H9 = start week, I9 = end week. See the picture.
You could subtract 90 days before calculating the week number. (Assuming the start is April 1 instead of January 1.)
Or, have a named cell indicating the first day of the Fiscal Year and then use that to calculate the week number, something like:
([Report date] - [1st day of F'Year]) + 7 = [Week Number]