Solved Incorrect Week Number Being shown

[Number11]

So Access is throwing out the report with the wrong week number., today 13.10.2022 is week 41 yet its showing as 42?

command used for naming the file is ...

.ActiveWorkbook.SaveAs ("C:\Reports\Zip\Sales Report Week " & Format(Date, "ww") & " " & Format(Date, "DD-MMM-YYYY") & ".xlsx")

output is..

 

[arnelgp]

i got same week number, 42
what is your expected week number?

 

[June7]

I also get 42. Why do you expect 41?

 

[Number11]

because UK week number is 41

Week Numbers for 2022 (epochconverter.com)

 

[arnelgp]

use this:

=datepart("ww",date,vbMonday,vbFirstFourDays)

reference:
VBA for smarties ISO weeknumber (snb-vba.eu)

 

[Number11]

Yeh fixed by using...

.ActiveWorkbook.SaveAs ("C:\Reports\Zip\Sales Report Week " & Format(Date, "WW", vbMonday, vbFirstFullWeek) & " " & Format(Date, "DD-MMM-YYYY") & ".xlsx")