week2date function different result on two machines

[phairplay]

Hi Guys,
I'm using the below function which turns a week number into a week

Public Function Week2Date(WeekNo As Long) As Date
     Dim Jan1             As Date
     Dim Sub1             As Boolean
     Dim Ret              As Date
    Jan1 = DateSerial(Year(Date), 1, 1)
     'Sub1 = (VBA.Format(Jan1, "ww", VBA.vbMonday, VBA.vbFirstFourDays) = 1)
     Sub1 = (Format(Jan1, "ww", vbUseSystem, vbUseSystem) = 1)
     Ret = DateAdd("ww", WeekNo + Sub1, Jan1)
     Ret = Ret - WeekDay(Ret) + 7
     Week2Date = Ret
 End Function

I use it to return the date 25 weeks from now

dateweek = DateValue((Week2Date((Format(Now, "ww") + 25))))

when I load the macro on another machine I noticed I was getting a different date

when running the following:

dateweek = DateValue((Week2Date((Format(Now, "ww")))))

my machine returned the correct result which is Week 45 yet on my other machine it returns a result of week 46

could someone please help me find out what is causing this issue?

many thanks

 

[wideboydixon]

I guess the logical reason is this:

     Sub1 = (Format(Jan1, "ww", vbUseSystem, vbUseSystem) = 1)

If the two different machines have different system settings for first day or week / first week of year then you could get different results.

WBD

 

[phairplay]

hi thank you for the response,
I've checked and both machines have the same region, date and time

one machine is windows 7 (the one with the correct week) the other is windows 10

is there away to fix this?

 

[wideboydixon]

Not sure your calculation is correct anyway; Week2Date(1) gives 14th January? How about this function instead?

Public Function Week2Date(weekNumber As Long) As Date

Dim testDate As Date

Week2DateWBD = DateSerial(Year(Date), 1, 1)
testDate = Week2DateWBD - Weekday(Week2DateWBD, vbMonday) + (weekNumber - 1) * 7 + 1
If testDate > Week2DateWBD Then Week2DateWBD = testDate

End Function

WBD