What Day of the Week Is This?

[sukishan]

What Day of the Week Is This?
In addition to working with months and days, you may need to know the day of the week represented by a date value. Of course, you could calculate this yourself (there are published algorithms for calculating the day of a week, given a date), but why bother? VBA knows the answer and can give it to you easily, using the built-in WeekDay function. (You can also use the DatePart function, discussed in the next section, to retrieve the same information.)

To determine the day of the week represented by any date value, use the WeekDay function. Supply it with a date value, and it will return the day of the week on which that date falls. For example,

Debug.Print WeekDay(#5/16/56#)returns 4, indicating that May 16 fell on a Wednesday in 1956.

Sunday Isn't Always the First Day of the Week

Online help indicates that you can pass a second parameter to WeekDay, indicating the first day of the week. In many countries, Monday is considered the first day of the week, so most of the VBA date functions allow you to specify what you consider to be the first day of the week. If you don't specify a value, VBA uses the Windows setting for your local country. If you specify a constant (vbSunday through vbSaturday) for this parameter, VBA treats that day as the first day of the week and offsets the return value accordingly.

For example, the following lines represent a sample session in the Immediate window (run in the United States, where Sunday is the first day of the week):

? WeekDay(#5/1/98#)
6
? WeekDay(#5/1/98#, vbUseSystemDayOfWeek)
6
? WeekDay(#5/1/98#, vbMonday)
5Note that as you change the value of the FirstDayOfWeek parameter, the return value changes as well. You need to be aware that WeekDay (and the corresponding functionality in the DatePart function) doesn't return a fixed value, but rather, a value relative to the local first day of the week.

Of course, if you want a fixed value, no matter where your code runs, simply specify the first day of the week. The following example returns 6 no matter where you run it:

? WeekDay(#5/1/98#, vbSunday)