can someone explain what i am seeing?

this expression returns 4
DatePart("w", DateSerial(2009, 1, 1), vbMonday, vbFirstFullWeek)

the 1st is on thursday and the first monday is the 5th, which is 4 days later.

but this expression returns 6, which doesn't make sense to me. i would think it
would return 3
DatePart("w", DateSerial(2010, 1, 1), vbMonday, vbFirstFullWeek)

so, i guess i'm missing something.


--


Gary

Contrary to what the help file says, I don't think the 4th argument has any
bearing on the return value from the DatePart function when the first
argument is "w" (try all the possible arguments for the 4th argument... for
a given date and start of week, you will always get the same value). Now, as
to what the function is returning... I don't think it is doing what you seem
to think it is. With the "w" argument, it is giving you the weekday number
of the date you provide with the count starting as indicated by the 3rd
argument. So, for this...

DatePart("w", DateSerial(2009, 1, 1), vbMonday, vbFirstFullWeek)

it returns 4 because the January 1, 2009 occurs on a Thursday which is the
4th day of the week when the week starts on Monday (Monday is 1, Tuesday is
2, Wednesday is 3 and Thursday is 4). As for this...

DatePart("w", DateSerial(2010, 1, 1), vbMonday, vbFirstFullWeek)

you said it returns 6, but actually, it returns 5 and that is because
January 1, 2010 occurs on Friday and Friday is the 5th day of the week when
the week starts on a Monday.

--
Rick (MVP - Excel)


"Gary Keramidas" <GKeramidasAtMsn.com> wrote in message
news:ObV%(E-Mail Removed)...
> can someone explain what i am seeing?
>
> this expression returns 4
> DatePart("w", DateSerial(2009, 1, 1), vbMonday, vbFirstFullWeek)
>
> the 1st is on thursday and the first monday is the 5th, which is 4 days
> later.
>
> but this expression returns 6, which doesn't make sense to me. i would
> think it would return 3
> DatePart("w", DateSerial(2010, 1, 1), vbMonday, vbFirstFullWeek)
>
> so, i guess i'm missing something.
>
>
> --
>
>
> Gary
>
>

thanks, rick. i mistyped and the 6 should have been 5.

--


Gary

"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Contrary to what the help file says, I don't think the 4th argument has any
> bearing on the return value from the DatePart function when the first argument
> is "w" (try all the possible arguments for the 4th argument... for a given
> date and start of week, you will always get the same value). Now, as to what
> the function is returning... I don't think it is doing what you seem to think
> it is. With the "w" argument, it is giving you the weekday number of the date
> you provide with the count starting as indicated by the 3rd argument. So, for
> this...
>
> DatePart("w", DateSerial(2009, 1, 1), vbMonday, vbFirstFullWeek)
>
> it returns 4 because the January 1, 2009 occurs on a Thursday which is the 4th
> day of the week when the week starts on Monday (Monday is 1, Tuesday is 2,
> Wednesday is 3 and Thursday is 4). As for this...
>
> DatePart("w", DateSerial(2010, 1, 1), vbMonday, vbFirstFullWeek)
>
> you said it returns 6, but actually, it returns 5 and that is because January
> 1, 2010 occurs on Friday and Friday is the 5th day of the week when the week
> starts on a Monday.
>
> --
> Rick (MVP - Excel)
>
>
> "Gary Keramidas" <GKeramidasAtMsn.com> wrote in message
> news:ObV%(E-Mail Removed)...
>> can someone explain what i am seeing?
>>
>> this expression returns 4
>> DatePart("w", DateSerial(2009, 1, 1), vbMonday, vbFirstFullWeek)
>>
>> the 1st is on thursday and the first monday is the 5th, which is 4 days
>> later.
>>
>> but this expression returns 6, which doesn't make sense to me. i would think
>> it would return 3
>> DatePart("w", DateSerial(2010, 1, 1), vbMonday, vbFirstFullWeek)
>>
>> so, i guess i'm missing something.
>>
>>
>> --
>>
>>
>> Gary
>>
>>
>