Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 72,460
- Office Version
- 365
- Platform
- Windows
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:
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)...
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:
Code:
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
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)...