Bug/ Incorrect Documentation in WeekdayName()

[Galaxiom]

As documented, WeekDay() uses a default of Sunday for the first day of the week.

The documentation for WeekDayName() also claims that its default for the first day of the week is Sunday. This is incorrect. In fact WeekDayName() uses the System Regional Setting for First Day of the Week.

This anomaly is not evident in regions where Sunday is the First Day of Week.

However the First Day of Week in the ISO Standard is Monday. Consequently there is an anomaly between Weekday() and WeekDayName() in regions such as Australia where ISO is used.

We must either change the Regional First Day of Week (potentially causing other issues) or use the First Day of Week argument in the WeekDayName function to force it back to Sunday so that it is consistent with WeekDay().

 

[gemma-the-husky]

is weekdayname a new function?

I use weekday, but have never used weekdayname, and can't recall seeing it.


Do you know, I just tested this - I never realised weekday would change based on firstdayofweek. I thought it was just a cardinal number for the day, and that

weekday(date) was a constant

ie, given today as a Monday

 Sub testweekday()
        If Weekday(Date) = vbMonday Then
            MsgBox ("Monday")
        Else
            MsgBox ("Not Monday")
        End If
end sub

I now see that the following does not work.

 Sub testweekday()
        If Weekday(Date, vbWednesday) = vbMonday Then
            MsgBox ("Monday")
        Else
            MsgBox ("Not Monday")
        End If
end sub

Who would have thought it!

 

[robslob]

This got me thinking away, so if the date is not constant then the formula must be along the lines of
=Clng(Date()) Mod 7
which for today 11/05/2015 will give 2 (or the 2nd day of the week)
so the offset is needed to realign the day depending of whether the 2nd day of the week is a Monday or Tuesday.

 

[Solo712]

is weekdayname a new function?

I use weekday, but have never used weekdayname, and can't recall seeing it.


Do you know, I just tested this - I never realised weekday would change based on firstdayofweek. I thought it was just a cardinal number for the day, and that

weekday(date) was a constant

ie, given today as a Monday

 Sub testweekday()
        If Weekday(Date) = vbMonday Then
            MsgBox ("Monday")
        Else
            MsgBox ("Not Monday")
        End If
end sub

I now see that the following does not work.

 Sub testweekday()
        If Weekday(Date, vbWednesday) = vbMonday Then
            MsgBox ("Monday")
        Else
            MsgBox ("Not Monday")
        End If
end sub

Who would have thought it!

Dave,
that's the whole point. The problem, as Galaxiom points out, is that Weekday() function does not "follow" the Regional settings. And the reason is - most likely - that the designers committed themselves to constants in naming the days (vbMonday - vbSunday) which would have to change values based on Regional Settings. So, it appears that the WeekdayName() function came as an "afterthought", that was not integrated into the original schema. The "bare-wire" registry code - Galaxiom will be happy to know - actually is following the ISO standard (0=Monday, 6=Sunday):

but the decision was to make "6" the default. This has no bearing on anything as long as you operate under the "Sunday-first" standard. Ducks are aligned. But if you are coding in regions that do not follow the US, then you need to specify the optional FirstDayOfWeek in the WeekDay() function. Bottom line is : if you want to avoid trouble, specify FirstDayOfWeek in WeekDay always !

Here is how you extract the regional setting and convert it to the Microsoft day-naming schema. Read in the Regional Setting first and then plug the converted value in the Weekday function().

Dim fdw as Long
fdw = CLng(RegKeyRead("HKCU\Control Panel\International\iFirstDayOfWeek")
 
'convert to Sunday first notation for vb dayname constants
fdw = (fdw + 1) Mod 7 + 1
 
MsgBox "Regional Ordinal for First Day of Week is " & Str(fdw)
 
'----------------
 
Public Function RegKeyRead(i_RegKey As String) As String
Dim myWS As Object
  On Error Resume Next
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'read key from registry
  RegKeyRead = myWS.RegRead(i_RegKey)
  Set myWS = Nothing
End Function