Here’s how to determine what week a date falls into for either a calendar or fiscal year:
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
You can use the WEEKNUM function in Excel to determine what week a certain date falls into for that year. Here is a layout of different dates and the syntax for the WEEKNUM function. Included is also the syntax for the DATE function, which I will talk about in a bit:
The [return_type] portion of the function determines what day of the week your week starts. The options are:
As you can see, I chose “2” for Monday. If you leave this blank, it will default to “1” for Sunday.
Getting the week for any date in a calendar year is easy. To get it for a fiscal year, you basically need to calculate the week for that date in a calendar year then subtract the week number for the date prior to the date when your fiscal year starts. Thus we have the formula:
Notice how I have (B4-1). This takes the date when my fiscal year starts (7/1/2014) and subtracts one day to indicate when the previous fiscal year ended (6/30/14).
The alternate formula:
does the same thing, but in this case I inserted the date rather than reference a cell location. To do this, however, you need to use the DATE function. This will return the serial number of the date. You can’t just put the date in the WEEKNUM function.