Brainteaser: Convert Day of Week to Number

[Joe4]

We have a situation where we have a table in a database that has a field representing "days of the week". It is formatted as Text (String) and is actually the day spelled out (i.e. "Monday").

I have to use this in some date calculations, so I need to convert this to a numerical value, i.e.
"Sunday" = 1
"Monday" = 2
...
"Saturday" = 7

It seems like it should be pretty easy to do, but I haven't found any functions that seem to do it (not many go from Text to Date formats).

So I created a UDF that does this, and does a fine job:

Function DayOfWeekValue(myDay As Variant) As Integer
'   Convert text value of day of week (i.e. "Monday") to numeric representation
 
    Select Case myDay
        Case "Sunday"
            DayOfWeekValue = 1
        Case "Monday"
            DayOfWeekValue = 2
        Case "Tuesday"
            DayOfWeekValue = 3
        Case "Wednesday"
            DayOfWeekValue = 4
        Case "Thursday"
            DayOfWeekValue = 5
        Case "Friday"
            DayOfWeekValue = 6
        Case "Saturday"
            DayOfWeekValue = 7
        Case Else
            DayOfWeekValue = 0
    End Select
            
End Function

The thing that bugs me is I can't help shake the feeling that there must be some cool formulaic way of doing this (that isn't too complex) that doesn't require a UDF, and I am just missing it.

Am I missing something easy?
Or does it require a UDF or a complex formula?

Really, this question is just to satisfy my own curiosity (as I said, I have a working solution)...

 

[james_lankford]

wonder how the performance would be you made a new table containing 2 fields and 7 rows --
DayOfWeek : NumberOfDay
Sunday 1
Monday 2
...
Saturday 7

and then in your current table, put an index on the DayofWeek field and then linked the 2 tables

 

[boblarson]

Here's a shorter version which was provided by Bob Askew on the Access World Forums website:

'*******************************************<?XML:NAMESPACE PREFIX = O /><O:P></O:P>
Public Function RtnMonthNum(pstrMonName As String) As Integer
'*******************************************
'Purpose:   Returns month number (1 - 12)
'           when provided a complete or partial
'           month name.
'Coded by:  raskew
'Input:     From debug (immediate) window:
'           1) ? RtnMonthNum("April")
'           2) ? RtnMonthNum("Sep")
'Output: 1) 4
'           2) 9
'*******************************************<O:P></O:P>
 <O:P></O:P>
Dim strHold  As String
Dim strMonth As String
Dim intMonth As Integer<O:P></O:P>
 <O:P></O:P>
   strMonth = "JanFebMarAprMayJunJulAugSepOctNovDec"
   strHold = Left(pstrMonName, 3)
   intMonth = InStr(strMonth, strHold)
   RtnMonthNum = intMonth \ 3 + 1<O:P></O:P>
 <O:P></O:P>
End Function

 

[Joe4]

Thanks James, thought of that too.

Bob, that was for months, not days, but I see one could apply the same logic.

But no formula solutions, eh?
Guess I didn't overlook anything obvious, which makes me feel better (I guess!).

 

[boblarson]

Oops, yeah, you could use the same type of setup. And no, there is no formula solution, although I wish they would add one.

 

[shg]

No suggestion for anything in Access, but a different UDF:

Function DayOfWeekValue(myDay As Variant) As Integer
    Const sDays As String = "Sunday    Monday    Tuesday   Wednesday Thursday  Friday    Saturday"
        DayOfWeekValue = (InStr(1, sDays, myDay, vbTextCompare) + 9) \ 10
End Function

 

[Kreszch68]

And here a little bit of all the above suggestions put together

Public Function DayOfWeek(ByVal vDay As Variant) As Integer
Const s As String = "SuMoTuWeThFrSa"
DayOfWeek = (InStr(1, s, Left(vDay, 2), vbTextCompare) + 1) / 2
End Function

 

[RichardMGreen]

At a (very) quick look around, you could use datepart("w",<date>) to get the number. It would need to be used ini a query though.

 

[Norie]

Does the code in post #6 (or #7) actually need to be in a UDF?

Can't you use an expression like this?

DayNum: (InStr(1,"Sunday Monday Tuesday Wednesday Thursday Friday Saturday",[FieldName],1)+9)\10

 

[taurean]

A function that probably doesn't look good:
=MATCH(A1,{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"},0)