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]

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

getting that to work access is the next brain teaser

 

[Joe4]

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.

Richard, DatePart only works on date fields, not Text representations of the day of the week.

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

Norie, I needed to fix the spaces so each day of the week is exactly 10 spaces, but that does indeed work! Congratulations! You came up with a formula solution!

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

Taurean, you must have thought you were in the Excel forum - this is an Access question.

 

[taurean]

Richard, DatePart only works on date fields, not Text representations of the day of the week.

Norie, I needed to fix the spaces so each day of the week is exactly 10 spaces, but that does indeed work! Congratulations! You came up with a formula solution!

Taurean, you must have thought you were in the Excel forum - this is an Access question.

The Oops moment...I was tad (maybe more than tad) careless and didn't see forum ...!

 

[RichardMGreen]

Richard, DatePart only works on date fields, not Text representations of the day of the week.
QUOTE]

Apologies, I thought you were working on actual dates.
Must has mis-read the OP.

 

[Kreszch68]

Norie, I needed to fix the spaces so each day of the week is exactly 10 spaces, .....

Not if you use the "SuMoTuWeThFrSa" string
If you combine Nories solution with this string and part of my function, you don't need to count the spaces.

 

[Joe4]

Not if you use the "SuMoTuWeThFrSa" string
If you combine Nories solution with this string and part of my function, you don't need to count the spaces.

Yep, that works too:

DayNum: (InStr(1,"SuMoTuWeThFrSa",Left([DayOfWeek],2),1)+1)\2

 

[Norie]

Couldn't SWITCH be used?

SWITCH([FieldName]='Sunday', 1, [FieldName] = 'Monday', 2... and so on )

It would end up a rather long expression though - I got bored just doing that bit above.

PS It was the board that stole the spaces.

 

[Joe4]

PS It was the board that stole the spaces.

Yes, the only way I have found to maintain spaces (when you have more than one in succession) seems to be use the Code Tags.

 

[Norie]

Or you could use H T M L .