![]() In my specific scenario I need to be able to do this both from 1 January for Calendar Years, and from 1 July for Financial Years. In short, is there a way to use the WEEKNUM function in Excel to just simply calculate week numbers on the basis of 7 day blocks of time. Column F shows the results of my present formula for calculating the Financial Year week number, but it isn't always producing the correct result (see red circles showing incorrect calculations).Column E shows the desired result of calculating the Financial Year week number (remembering that a Financial Year runs from 1st July to 30th June).I also can't seem to adapt it for calculating Financial Year weeks. The results are accurate, but since I'm using the IF function to specify the calculation depending on the year, it's really not ideal as it means we need to manually change the formula whenever we get data for a different year. Column C shows the results of my very long formula above for calculating the Calendar Year week number.This formula is as follows: + the formula doesn't always calculate the correct result (more details in screenshot below). My current formula for Financial Year - concise, but not 100% accurateĪfter spending a lot of time on trying to adapt my Calendar Year formula to calculate Financial Year week numbers, I eventually gave up and came up with something else that seems fairly close to what I need. This way there would be no need for manually adding more IF functions as more years appear in the data source. Instead, I was hoping to have a much simpler formula that would just look at the date from the Form_Finalised_Date column and work out the week number by effectively starting on 1 January and counting each 7 days as a week and so on. seems unnecessarily long as I've had to write it to allow for the next eight years to try and future proof it!.can't be adapted to calculate Financial Year week numbers (at least I haven't found a way to do it).In terms of a formula for calculating the Calendar Year week number, I've had to settle for the following: you can see, to get the desired result for Calendar Year weeks I've had to determine what weekday 1st January falls on for each of the years between 2015 to 2028 so I can add the corresponding Return_type argument for the year. My current formula for Calendar Year - it's accurate, but. ![]()
0 Comments
Leave a Reply. |