By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
 446,285 Members | 1,647 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,285 IT Pros & Developers. It's quick & easy.

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

 P: n/a 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 Nov 12 '05 #1
Share this Question
9 Replies

 P: n/a 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(useDate as Date) As String Dim dayNum as Integer If Not IsDate(useDate) Then Exit Function dayNum = DatePart("w",Date) 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",Date). 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 www.veraccess.com "PamelaDV" wrote in message news:b6**************************@posting.google.c om... 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 Nov 12 '05 #2

 P: n/a 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" wrote in message news:b6**************************@posting.google.c om... 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 Nov 12 '05 #3

 P: n/a Check the help files for WeekDay(). -- ================= Jeremy Wallace AlphaBet City Dataworks ABCDataworks dot com "PamelaDV" wrote in message news:b6**************************@posting.google.c om... 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 Nov 12 '05 #4

 P: n/a On 4 Mar 2004 12:20:16 -0800, pd******@steelcase.com (PamelaDV) wrote: I have two problems, actually. I am looking to see if there is afunction that will return the day of the week (Monday, Tuesday,Wednesday, etc...) from a date. For instance 2/27/04 is a Friday. See help for the Format() function. Also is there a way to group dates by week? I have a user who wants areport to group by week. I know I could create a table and assigndates to a week, but I'm wondering if anyone has anything out therealready done that may be a little bit simpler. Best Practice is to use a table. -- Mike Sherrill Information Management Systems Nov 12 '05 #5

 P: n/a pd******@steelcase.com (PamelaDV) wrote in news:b6783f18.0403041220.39301038 @posting.google.com: 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. 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) Nov 12 '05 #6

 P: n/a 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" wrote in message news:jp********************************@4ax.com... On 4 Mar 2004 12:20:16 -0800, pd******@steelcase.com (PamelaDV) wrote:I have two problems, actually. I am looking to see if there is afunction that will return the day of the week (Monday, Tuesday,Wednesday, etc...) from a date. For instance 2/27/04 is a Friday. See help for the Format() function.Also is there a way to group dates by week? I have a user who wants areport to group by week. I know I could create a table and assigndates to a week, but I'm wondering if anyone has anything out therealready done that may be a little bit simpler. Best Practice is to use a table. -- Mike Sherrill Information Management Systems Nov 12 '05 #7

 P: n/a On 6 Mar 2004 13:48:07 GMT, Lyle Fairfield wrote: pd******@steelcase.com (PamelaDV) wrote in news:b6783f18.0403041220.39301038@posting.google.com: 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.An expression likeDatePart("ww", vDate, vbSunday, vbFirstJan1)can return a week of the year.The enumerators vbSunday, vbFirstJan1 can be manipulated to modify theresults in keeping with your local practice. 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 Nov 12 '05 #8

 P: n/a Plus the wrong year, in certain cases. It was worse pre-A2K! Here is my solution: Public Function getISODate(varDate 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\$(intDay), 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" wrote in message news:3s********************************@4ax.com... On 6 Mar 2004 13:48:07 GMT, Lyle Fairfield wrote:pd******@steelcase.com (PamelaDV) wrote in news:b6783f18.0403041220.39301038@posting.google.com: 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.An expression likeDatePart("ww", vDate, vbSunday, vbFirstJan1)can return a week of the year.The enumerators vbSunday, vbFirstJan1 can be manipulated to modify theresults in keeping with your local practice. 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 Nov 12 '05 #9

 P: n/a Wayne Gillespie wrote in news:3s********************************@4ax.com: On 6 Mar 2004 13:48:07 GMT, Lyle Fairfield wrote:pd******@steelcase.com (PamelaDV) wrote innews:b6783f18.0403041220.39301038 @posting.google.com: 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.An expression likeDatePart("ww", vDate, vbSunday, vbFirstJan1)can return a week of the year.The enumerators vbSunday, vbFirstJan1 can be manipulated to modify theresults in keeping with your local practice. 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. 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) Nov 12 '05 #10

### This discussion thread is closed

Replies have been disabled for this discussion.