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
Bytes IT Community
+ 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
Share on Google+
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" <pd******@steelcase.com> 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" <pd******@steelcase.com> 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" <pd******@steelcase.com> 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 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.
See help for the Format() function.
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.


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
CDB
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" <MS*************@compuserve.com> 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 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.


See help for the Format() function.
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.


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 <Mi************@Invalid.Com> 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 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.

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
CDB
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" <be*****@NObestfitsoftwareSPAM.com.au> wrote in message
news:3s********************************@4ax.com...
On 6 Mar 2004 13:48:07 GMT, Lyle Fairfield <Mi************@Invalid.Com> 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 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.

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 <be*****@NObestfitsoftwareSPAM.com.au> wrote in
news:3s********************************@4ax.com:
On 6 Mar 2004 13:48:07 GMT, Lyle Fairfield <Mi************@Invalid.Com>
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 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.

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.