Custom Week Number - Last Saturday in December is Week 1

  • Hello all,


    Just had to reset to a new account as couldn't login. Anyway, I have an issue which im wondering someone can help.


    I need a formula to work out a custom week number whereby the Saturday AFTER last Friday in December is Week 1 of the year. Is there a formula that can derive this or do I have to use vba (I think I do a custom function in VBA).


    Pretty sure its a combo of weeknum/weekday/date and working out the days, but I haven't got it working perfectly. Friday 31/12/2021 would be week 53 for instance, but 01/01/2022 would be week 1. 25/12/20 would be week 52, Sat 29/12/18 would be week 1 etc etc. Can anyone help? Normally I solve it within an hour of asking for help so hoping the charm works again!

  • maybe


    =IF(WEEKNUM(DATE(YEAR(TODAY()),12+1,0)+MOD(-WEEKDAY(DATE(YEAR(TODAY()),12+1,0),2)-2,-7))=52,WEEKNUM(DATE(YEAR(TODAY()),12+1,0)+MOD(-WEEKDAY(DATE(YEAR(TODAY()),12+1,0),2)-2,-7))-51,WEEKNUM(DATE(YEAR(TODAY()),12+1,0)+MOD(-WEEKDAY(DATE(YEAR(TODAY()),12+1,0),2)-2,-7))-52)



    NBVC can probably come upwith something better

  • Thanks Roy. It doesn't seem to quite work as gives answer of 1 against any date (I replaced now with cell) the date values - it wasn't a million miles from where I was, but I think I need a nested if for week 53 (I don't think 54 is possible)


    =IF(WEEKNUM(DATE(YEAR(B2),12+1,0)+MOD(-WEEKDAY(DATE(YEAR(B2),12+1,0),2)-2,-7))=52,WEEKNUM(DATE(YEAR(B2),12+1,0)+MOD(-WEEKDAY(DATE(YEAR(B2),12+1,0),2)-2,-7))-51,WEEKNUM(DATE(YEAR(B2),12+1,0)+MOD(-WEEKDAY(DATE(YEAR(B2),12+1,0),2)-2,-7))-52)


    Looks like the ball park I was in though so will try an persevere.

  • A different take on this. If you only need this for a relatively modest range of years, a table-based approach may be most efficient. Say the table were in X2:Y29 with 2015 in X2, =X2+1 in X3, with X3 filled down into X4:X29, so 2015 to 2042. These years would begin on the last Saturday of the previous year, so for 2015, =DATE(X2,1,1)-WEEKDAY(DATE(X2,1,1)) in Y2. Fill Y2 down into Y3:Y29. Then name Y2:Y29 YBTable.


    Given a date between the last Saturdays in 2014 and 2042, the week number for a date in cell Z99 would be given by


    =1+INT((Z99-LOOKUP(Z99,YBTable))/7)


    If you REALLY have to do this in a single formula,


    =1+INT((Z99-

        IF(DATE(YEAR(Z99)+1,1,1)-WEEKDAY(DATE(YEAR(Z99)+1,1,1))<=Z99,

          DATE(YEAR(Z99)+1,1,1)-WEEKDAY(DATE(YEAR(Z99)+1,1,1)),

          DATE(YEAR(Z99),1,1)-WEEKDAY(DATE(YEAR(Z99),1,1))))

        /7)

  • Once you have tested the formula ... feel free to comment

    Hello


    Sorry for the delay in responding - I had to work on another urgent project than holiday took hold! Happy New Year to you all!


    Ok so I have tested the formula, but it doesn't seem to work quite how I wanted. Basically I want to enter any date and give me the correct week number for that year using the logic of the Saturday after last Friday of the year is week 1.


    I didn't want to go through a lookup table as it should be relatively straight forward - and now it is bloody mindedness that im trying to solve it. On thinking again, I would also need the correct year calculated. Im starting to think VBA might be the only way forward. I have attached the table that is being used currently. What I wish to do is type in a "real date" and this give me the weeknumber and year.

  • Hello,


    Sorry but your Sample file does not show your expected result ...


    Attached is Version 2 ... my own guess about what you are looking for ...

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hello Carim


    Thank you for responding. The attachment should show a date in column B and the corresponding Week in column E done as a simple table. What I want to do is put in any date and it tell me the corresponding week and year. For instance, if I type in 26/12/2020, it would give me a week Number 1, Year 2021 ("01-2021") as the result. I want this as a formula so I can put in any date and get the correct week number-Year combo without using a table (the result is shown in column J). I don't want to build a function because this logic would be used across multiple books. From what you have sent I think I can get the result I want, I just need to change the reference date with some additional formula to link with the date in cell A7(because it will always be the last Saturday of that year).

  • Hello,


    Thanks for your additional info about your sample file ...


    Have you tried to adapt the formula to your file ... ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hello,


    I am just playing with it now. However, I think there may be an issue giving the correct year (especially if the date is at the end of December because the last Saturday may be before the date put in but it will be week 1 of the following year). The bigger question is why they don't follow the WEEKNUM function but there we go! Perhaps it is just one of those things that sound easier to do than reality

  • Hello,


    There are several international versions of what a week number is ... (standard, ISO, etc...)


    You are after a fiscal week numbering system ... which, by definition, is unique ...


    So customization is he only way out ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Ok so this is what I came up with. It seems to match the table until 2030. Now I need to work out the reverse (ie put in week number and year and it will give me the date of the Saturday - this is because they have reports in this format but for excel to calculate I actually need the dates otherwise it gets messy). The key to the below was when the date was bigger than the last Saturday because you then have to add a year.



  • ...

    I need a formula to work out a custom week number

    ...


    Re,


    In your very first message, seems you needed ... a formula ...


    and now you are coming up with a macro ...?(


    What solution has your preference ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Well I did want a formula but I just wanted to solve it without using a table. I thought the above would help.


    Next step is now to use that logic above to make it into a single cell formula (with a reference to one cell for the date to convert). I also need to work it back from the week-Year to give a date using the same logic in reverse (some sheets just have the week-year, but I need to convert into a date for calculations).


    Sorry if it seems a messy way of doing things, but there is a lot of work that im "finding"

  • Hello again,


    It is rather confusing ...?(


    You are saying ' next step is ...'


    Does it mean that the first step is now solved or not ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Sorry for not being clear, I thought I had been, honestly!



    By first step I meant I had no idea how to solve it at all, now I do I just need to put this code into a formula. You helped me with the approach so am very grateful. I still need to put this into a formula but hopefully I can do that via nested if. Once done, I then need to do the reversal of week and year to give a date.

  • Thanks for the clarification ...


    Before getting to steps 2, 3 and 4 ...


    Let's make sure step 1 ... or more clearly ...the answer to your very first message ... is satisfactory ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)