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!

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.

Quote from Carim

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 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,

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

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.

Option Explicit
Dim Dte As Date, LstSat As Date
Dim Wk As Long, Yr As Long, Rw As Long

Sub WeekNumRR()
Rw = 2

Do Until Cells(Rw, 2) = ""

Dte = Cells(Rw, 2)
LstSat = (WorksheetFunction.EoMonth(DateSerial(Year(Dte), 12, Day(Dte)), 0) + 1 - WorksheetFunction.Weekday(WorksheetFunction.EoMonth(DateSerial(Year(Dte), 12, Day(Dte)), 0) + 1 - 6))

If Dte <= LstSat Then
'we need to take previous year last sat date as that is what we are going from
LstSat = (WorksheetFunction.EoMonth(DateSerial(Year(Dte) - 1, 12, Day(Dte)), 0) + 1 - WorksheetFunction.Weekday(WorksheetFunction.EoMonth(DateSerial(Year(Dte) - 1, 12, Day(Dte)), 0) + 1 - 6))
Wk = WorksheetFunction.RoundUp((Dte - LstSat) / 7, 0)
Yr = Year(Dte)

Else
'we need to add a year
Wk = WorksheetFunction.RoundUp((Dte - LstSat) / 7, 0)
Yr = Year(Dte) + 1
End If

Cells(Rw, 11) = "'" & Wk & "-" & Yr

Rw = Rw + 1

Loop


End Sub

[/code]

Shame I couldn't get this in formula but there we go. I always vba my way out!

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"

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.