Convert Date from yyyymmdd format to numeric day of week (1=M, 2=T

  • Thread starter Thread starter kemosabe
  • Start date Start date
K

kemosabe

Is there a simple way to convert a date from yyyymmdd format to a numeric
representation of day of week (1=Monday, 2=Tuesday, etc.)? Thanks in advance!
 
Is there a simple way to convert a date from yyyymmdd format to a numeric
representation of day of week (1=Monday, 2=Tuesday, etc.)? Thanks in advance!
Click to expand...

Is the field actually a Date datatype:
Use an unbound text control and set it's control source to:

=Weekday([DateField])

Note: Sunday is the default 1st day of the week value.
If you want Monday to be the first day of the week you need to tell
Access.
=Weekday([DateField],2)
 
Yes. Use the weekday function on a date will return a value from 1 to 7.

So, is the date a datetime field or is it a string or is it some type of
number value.

Weekday(DateField,2) will return 1 (Monday) to 7 (Sunday)

If the field or variable is a string type then the expression is a little more
complex. Warning This will error if the value of the field is NULL or can't
be converted to a proper date.

Weekday(CDate(Format(StringDateField,"@@@@-@@-@@")),2)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
You first need to convert it to a date format. Here is how to do that
using DateSerial function.

lets say numVar of type long is in yyyymmdd format, dateVar is of type
date


dateVar = DateSerial(CInt([numVar],4)),CInt(Mid$([numVar],5,2)),CInt
(Right$([numVar],2)))


Then use Karl's above suggestion to resolve the day of week. Note that
Sunday = 1, Monday = 2, etc

Format(dateVar, "w")
 
typo in that date serial function (i forgot the Left() function,
should be:


dateVar = DateSerial(CInt(Left$([numVar],4)),CInt(Mid$([numVar],
5,2)),CInt(Right$([numVar],2)))
 
Back
Top