How determine what day of the week a certain date is?

[PamelaDV]

I have two problems, actually. I am looking to see if there is a
function that will return the day of the week (Monday, Tuesday,
Wednesday, etc...) from a date. For instance 2/27/04 is a Friday.

Also is there a way to group dates by week? I have a user who wants a
report to group by week. I know I could create a table and assign
dates to a week, but I'm wondering if anyone has anything out there
already done that may be a little bit simpler.

Thanks!

Pamela

[Mike Storr]

Re: How determine what day of the week a certain date is?

Off the top of my head, I've never seen anything that will will return
Friday or Monday, but it wouldn't be to hard to make a function that does
this. The only problem will be if regional settings affect what day is
considered the first.

Function GetDayName(useD ate as Date) As String
Dim dayNum as Integer

If Not IsDate(useDate) Then Exit Function
dayNum = DatePart("w",Da te)
Select Case dayNum
Case 1
GetDayName = "Sunday"
Case 2
GetDayName = "Monday"
Case 3
GetDayName = "Tuesday"
etc...

End Select
End Function

As for the second question, you can get the week number (1-53 I beleive) by
using DatePart("ww",D ate). Perhaps this number could be used to sort or
group on in a query. Alternatively, you'd have to use a range as a string as
the sorting grouping value ie. "02/01/04 - 05/25/04.

Mike Storr

https://www.veraccess.com

"PamelaDV" <pdevries@steel case.com> wrote in message
news:b6783f18.0 403041220.39301 038@posting.goo gle.com...[color=blue]
> I have two problems, actually. I am looking to see if there is a
> function that will return the day of the week (Monday, Tuesday,
> Wednesday, etc...) from a date. For instance 2/27/04 is a Friday.
>
> Also is there a way to group dates by week? I have a user who wants a
> report to group by week. I know I could create a table and assign
> dates to a week, but I'm wondering if anyone has anything out there
> already done that may be a little bit simpler.
>
> Thanks!
>
> Pamela[/color]

[paii, Ron]

Re: How determine what day of the week a certain date is?

For day of week try Format$(NOW()," dddd")

In your report, use SORTING AND GROUPING to sort on the date field, then set
GROUP ON to "Week".
If you also want to sort the days of the week, setup a 2nd sort on the same
date field and set GROUP ON to "Each value".

"PamelaDV" <pdevries@steel case.com> wrote in message
news:b6783f18.0 403041220.39301 038@posting.goo gle.com...[color=blue]
> I have two problems, actually. I am looking to see if there is a
> function that will return the day of the week (Monday, Tuesday,
> Wednesday, etc...) from a date. For instance 2/27/04 is a Friday.
>
> Also is there a way to group dates by week? I have a user who wants a
> report to group by week. I know I could create a table and assign
> dates to a week, but I'm wondering if anyone has anything out there
> already done that may be a little bit simpler.
>
> Thanks!
>
> Pamela[/color]

[Jeremy Wallace]

Re: How determine what day of the week a certain date is?

Check the help files for WeekDay().

--
=============== ==
Jeremy Wallace
AlphaBet City Dataworks
ABCDataworks dot com
"PamelaDV" <pdevries@steel case.com> wrote in message
news:b6783f18.0 403041220.39301 038@posting.goo gle.com...[color=blue]
> I have two problems, actually. I am looking to see if there is a
> function that will return the day of the week (Monday, Tuesday,
> Wednesday, etc...) from a date. For instance 2/27/04 is a Friday.
>
> Also is there a way to group dates by week? I have a user who wants a
> report to group by week. I know I could create a table and assign
> dates to a week, but I'm wondering if anyone has anything out there
> already done that may be a little bit simpler.
>
> Thanks!
>
> Pamela[/color]

[Mike Sherrill]

Re: How determine what day of the week a certain date is?

On 4 Mar 2004 12:20:16 -0800, pdevries@steelc ase.com (PamelaDV) wrote:
[color=blue]
>I have two problems, actually. I am looking to see if there is a
>function that will return the day of the week (Monday, Tuesday,
>Wednesday, etc...) from a date. For instance 2/27/04 is a Friday.[/color]

See help for the Format() function.
[color=blue]
>Also is there a way to group dates by week? I have a user who wants a
>report to group by week. I know I could create a table and assign
>dates to a week, but I'm wondering if anyone has anything out there
>already done that may be a little bit simpler.[/color]

Best Practice is to use a table.

--
Mike Sherrill
Information Management Systems

[Lyle Fairfield]

Re: How determine what day of the week a certain date is?

pdevries@steelc ase.com (PamelaDV) wrote in news:b6783f18.0 403041220.39301 038
@posting.google .com:
[color=blue]
> Also is there a way to group dates by week? I have a user who wants a
> report to group by week. I know I could create a table and assign
> dates to a week, but I'm wondering if anyone has anything out there
> already done that may be a little bit simpler.[/color]

An expression like

DatePart("ww", vDate, vbSunday, vbFirstJan1)

can return a week of the year.

The enumerators vbSunday, vbFirstJan1 can be manipulated to modify the
results in keeping with your local practice.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[CDB]

Re: How determine what day of the week a certain date is?

Or WeekDayName() function.

I followed Mike's suggestion of using a table a few years back, and it has
proved to be invaluable. The only system I know which handles the week
across the year change is the ISO system. My USA clients seem entirely happy
with the result - they probably do not realise that it is (gasp)
International!

Clive


"Mike Sherrill" <MSherrillnonon o@compuserve.co m> wrote in message
news:jpah40tcro lrgr5s68s7b5j54 15mt623lo@4ax.c om...[color=blue]
> On 4 Mar 2004 12:20:16 -0800, pdevries@steelc ase.com (PamelaDV) wrote:
>[color=green]
> >I have two problems, actually. I am looking to see if there is a
> >function that will return the day of the week (Monday, Tuesday,
> >Wednesday, etc...) from a date. For instance 2/27/04 is a Friday.[/color]
>
> See help for the Format() function.
>[color=green]
> >Also is there a way to group dates by week? I have a user who wants a
> >report to group by week. I know I could create a table and assign
> >dates to a week, but I'm wondering if anyone has anything out there
> >already done that may be a little bit simpler.[/color]
>
> Best Practice is to use a table.
>
> --
> Mike Sherrill
> Information Management Systems[/color]

[Wayne Gillespie]

Re: How determine what day of the week a certain date is?

On 6 Mar 2004 13:48:07 GMT, Lyle Fairfield <MissingAddress @Invalid.Com> wrote:
[color=blue]
>pdevries@steel case.com (PamelaDV) wrote in news:b6783f18.0 403041220.39301 038
>@posting.googl e.com:
>[color=green]
>> Also is there a way to group dates by week? I have a user who wants a
>> report to group by week. I know I could create a table and assign
>> dates to a week, but I'm wondering if anyone has anything out there
>> already done that may be a little bit simpler.[/color]
>
>An expression like
>
>DatePart("ww ", vDate, vbSunday, vbFirstJan1)
>
>can return a week of the year.
>
>The enumerators vbSunday, vbFirstJan1 can be manipulated to modify the
>results in keeping with your local practice.[/color]


Q200299 BUG: Format or DatePart Functions Can Return Wrong Week Number for Last Monday in Year

The article includes a function which returns the correct weeknumber to the ISO standard.


Wayne Gillespie
Gosford NSW Australia

[CDB]

Re: How determine what day of the week a certain date is?

Plus the wrong year, in certain cases. It was worse pre-A2K! Here is my
solution:

Public Function getISODate(varD ate As Variant) As String
'revised 13/5/03 to patch bug in Oleaut32.dll
Dim dtDate As Date
Dim intYear As Integer
Dim intWeek As Integer
Dim intDay As Integer
Dim intDayOfYear As Integer
Dim strRslt As String
On Err GoTo procerr
strRslt = "0000000"
If IsDate(varDate) Then
dtDate = CDate(varDate)
intYear = Year(dtDate)
If DatePart("ww", dtDate + 7, vbMonday, vbFirstFourDays ) = 2 Then
intWeek = 1
Else
intWeek = DatePart("ww", dtDate, vbMonday, vbFirstFourDays )
End If
intDay = Weekday(dtDate, vbMonday)
intDayOfYear = DatePart("y", dtDate, vbMonday, vbFirstFourDays )
Select Case intDayOfYear
Case Is < 4
If intDay > 4 Then intYear = intYear - 1
Case Is > 362
If intDay < 4 Then intYear = intYear + 1
End Select
strRslt = CStr(intYear) & right$("0" & intWeek, 2) &
right$(Str$(int Day), 1)
End If
procexit:
On Error Resume Next
getISODate = strRslt
Exit Function
procerr:
MsgBox Err.Description
Resume procexit
End Function

Public Function getISOWk(mydate As Variant) As String
getISOWk = Mid(getISODate( mydate), 5, 2)
End Function

Clive


"Wayne Gillespie" <bestfit@NObest fitsoftwareSPAM .com.au> wrote in message
news:3smk405va0 4d3fd63a7se3fnb 125qd8h8p@4ax.c om...[color=blue]
> On 6 Mar 2004 13:48:07 GMT, Lyle Fairfield <MissingAddress @Invalid.Com>[/color]
wrote:[color=blue]
>[color=green]
> >pdevries@steel case.com (PamelaDV) wrote in[/color][/color]
news:b6783f18.0 403041220.39301 038[color=blue][color=green]
> >@posting.googl e.com:
> >[color=darkred]
> >> Also is there a way to group dates by week? I have a user who wants a
> >> report to group by week. I know I could create a table and assign
> >> dates to a week, but I'm wondering if anyone has anything out there
> >> already done that may be a little bit simpler.[/color]
> >
> >An expression like
> >
> >DatePart("ww ", vDate, vbSunday, vbFirstJan1)
> >
> >can return a week of the year.
> >
> >The enumerators vbSunday, vbFirstJan1 can be manipulated to modify the
> >results in keeping with your local practice.[/color]
>
>
> Q200299 BUG: Format or DatePart Functions Can Return Wrong Week Number for[/color]
Last Monday in Year[color=blue]
>
> The article includes a function which returns the correct weeknumber to[/color]
the ISO standard.[color=blue]
>
>
> Wayne Gillespie
> Gosford NSW Australia[/color]

[Lyle Fairfield]

Re: How determine what day of the week a certain date is?

Wayne Gillespie <bestfit@NObest fitsoftwareSPAM .com.au> wrote in
news:3smk405va0 4d3fd63a7se3fnb 125qd8h8p@4ax.c om:
[color=blue]
> On 6 Mar 2004 13:48:07 GMT, Lyle Fairfield <MissingAddress @Invalid.Com>
> wrote:
>[color=green]
>>pdevries@stee lcase.com (PamelaDV) wrote in
>>news:b6783f18 .0403041220.393 01038 @posting.google .com:
>>[color=darkred]
>>> Also is there a way to group dates by week? I have a user who wants a
>>> report to group by week. I know I could create a table and assign
>>> dates to a week, but I'm wondering if anyone has anything out there
>>> already done that may be a little bit simpler.[/color]
>>
>>An expression like
>>
>>DatePart("ww" , vDate, vbSunday, vbFirstJan1)
>>
>>can return a week of the year.
>>
>>The enumerators vbSunday, vbFirstJan1 can be manipulated to modify the
>>results in keeping with your local practice.[/color]
>
>
> Q200299 BUG: Format or DatePart Functions Can Return Wrong Week Number
> for Last Monday in Year
>
> The article includes a function which returns the correct weeknumber to
> the ISO standard.[/color]

Thank you for drawing this to my attention.

Is any error returned for
DatePart("ww", vDate, vbSunday, vbFirstJan1)?

Does the error occur only for
DatePart("ww", vDate, vbMonday, vbFirstFourDays )?
[that is for calculating the ISO 8601 standard week number?]

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)