Dates

Hi Pia,

Welcome to the Support Forum and thank you for the question.

If you want the first week in August to be week 1 then you need to use the WEEKNUM function. This will turn any date to the equivalent week number starting in January by default.

The question is if you want week 1 to start on 01/08/23 and end on 07/08/23 then you need to include in the WEEKNUM a number which represents the first day of the week. So 01/08/23 falls on a Tuesday therefore it would be =WEEKNUM(date,12)

Let's say you had a range of dates from 01/08/23 to 31/07/24 covering the cell range A1:A366. The formula in cell B1 would be:

=IF(WEEKNUM(A1,12)>=32,WEEKNUM(A1,12)-31,WEEKNUM(A1,12)+21)

Because the week number of 01/08/23 - 07/08/23 is normally 32 then subtract 31 to get 1 for that first week, subtract 31 from the next week to get 2 etc.

This works when you copy down the formula into the next year. However where it falls down at the end is the last 2 days of July become 1 when they should be 53. These could be changed manually and then for the following year starting in 01/08/24 the weekday is Thursday so the formula would need to change to =WEEKNUM(date, 14). The last day in July 2025 would need to be adjusted to week 53 and then the formula for the dates from 01/08/25 would have to be changed again to allow for this date starting on a Friday (equivalent number in the WEEKNUM is 15)

I hope this helps. Please let me know if it has worked or not

Kind regards
Martin