=IF(OR(AND - How to make gantt with automatic calendar handle start date prior to 31th of Dec and end date later than 1st of Jan

Question

I made a gantt template where tasks are entered with a start date and an end date. Based on that I get a week number and use it to automatically fill cells in the calendar part of the sheet.

As our company have a fiscal year that start on the 1/7 and end on the 30/6. I run in to problems when the start date is less then or equal to 52 and end date is equal or bigger than 1. Can someone help me solve this?

https://docs.google.com/spreadsheets/d/1etVR3Y-l1hTVi7afdLJArq0juBFkXVI04r0SWwuAfCU/edit?usp=sharing

Answer 1

Try this:

=IF($A8 > $B8,
              IF(OR(AE$3>=$A8,AE$3<=$B8),"Y",""),
              IF(AND(AE$3>=$A8,AE$3<=$B8),"Y","")
 )

I just added an outer IF to calculate if the start week was greater than the end week. If so, instead of using AND, it uses OR.