Identifying Weekdays

[mlv]

Hi,

I have a cell with a drop-down list that allows the user to select any day
of the week, Sunday through to Monday.

Once the selection has been made, the chosen day appears in the cell as
text.

I need to use the chosen day in a date formula, so need to extract it as its
'day number', i.e. Sunday =1, Monday = 2...... Saturday = 7.

What is the easiest way to do this?

Thanks,

 

[RagDyer]

Does this work for you:

=LOOKUP(A1,{"Friday","Monday","Saturday","Sunday","Thursday","Tuesday","Wednesday",0;6,2,7,1,5,3,4,""})

 

[Dave Peterson]

You could use a formula like:

=MATCH(A1,{"Sunday","Monday","Tuesday","Wednesday","Thursday",
"Friday","Saturday"},0)

 

[Bernd P]

Hello,

Or
=MATCH(LOWER(A1),
{"sunday","monday","tuesday","wednesday","thursday","friday","saturday"},
0)
to account for all upper/lower case variants as well.

Regards,
Bernd

 

[T. Valko]

I have a drop-down list that allows the user

to select any day of the week, Sunday
through to Monday.

Try this:

A1 = drop down list

=SEARCH(LEFT(A1,2),"xSuMoTuWeThFrSa")/2

 

[T. Valko]

to account for all upper/lower case variants as well.

MATCH isn't case sensitive so LOWER()/UPPER() aren't needed.

 

[mlv]

Does this work for you:

=LOOKUP(A1,{"Friday","Monday","Saturday","Sunday","Thursday","Tuesday","Wednesday",0;6,2,7,1,5,3,4,""})

The formula works just fine.

Is there any particular reason why you put the day names (and therefore the
numbers) out of chronological order?

Thanks

 

[mlv]

You could use a formula like:

=MATCH(A1,{"Sunday","Monday","Tuesday","Wednesday","Thursday",
"Friday","Saturday"},0)

That's cunning, using the item relative position number to give the weekday
number ;-)

The formula works fine.

Thanks,

 

[mlv]

Try this:

A1 = drop down list

=SEARCH(LEFT(A1,2),"xSuMoTuWeThFrSa")/2

It took me a little while to get my head round this formula.

I now see why you used the prefix 'x' and divide by 2 - a consequence of
both Tuesday and Thursday beginning with the same letter, requiring search
to look for the first two letters of each weekday.

The formula works fine.

So many solutions to choose from!

Thanks for all your help.

 

[Dana DeLouis]

I have a cell with a drop-down list

Hi. Just throwing this out.
If you used Data - Validation to make your drop-down, and used "list" to
point to an area where your data is (perhaps, named "Tbl"), maybe you
could use the same list.

=MATCH(A1,Tbl,0)

= = = = = =
Dana DeLouis

 

[T. Valko]

=SEARCH(LEFT(A1,2),"xSuMoTuWeThFrSa")/2

Here's how it works...

SEARCH returns the starting position of a substring within a string as the
character number. For example:

SEARCH("T","xytz") = 3

The substring T starts at the 3rd character in the string.

Since some weekday names have the same first letter (Tuesday and Thursday,
Saturday and Sunday) we use the first 2 characters of each weekday name to
give that weekday name a unique search pattern.

SuMoTuWeThFrSa

If your drop down selection was Thursday we want to look for the the first 2
characters of Thursday in the string SuMoTuWeThFrSa.

SEARCH(LEFT(A1,2),"SuMoTuWeThFrSa")
SEARCH("Th","SuMoTuWeThFrSa")

"Th" starts at character number 9 but we need to somehow make "Th"
correspond to weekday number 5. By "padding" the the string SuMoTuWeThFrSa
with a character that is guaranteed not to appear in any of the substrings
we're looking for this shifts "th" from starting character 9 to starting
character 10. We can easily convert 10 to 5 by dividing by 2.

So, using the string xSuMoTuWeThFrSa we get:

Weekday......Char...Weekday number
Sunday..........2/2.............1
Monday........4/2.............2
Tuesday........6/2.............3
Wednesday...8/2.............4
Thursday.....10/2.............5
Friday.........12/2.............6
Saturday.....14/2.............7

 

[RagDyeR]

The Lookup() function needs it's values in *ascending order*,
and this configuration leaves the cell empty when no choice has been made in
the "drop-down" cell.


--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Does this work for you:

=LOOKUP(A1,{"Friday","Monday","Saturday","Sunday","Thursday","Tuesday","Wednesday",0;6,2,7,1,5,3,4,""})

The formula works just fine.

Is there any particular reason why you put the day names (and therefore the
numbers) out of chronological order?

Thanks

 

[mlv]

Here's how it works...

SEARCH returns the starting position of a substring within a string as the
character number. For example:

SEARCH("T","xytz") = 3

The substring T starts at the 3rd character in the string.

Since some weekday names have the same first letter (Tuesday and Thursday,
Saturday and Sunday) we use the first 2 characters of each weekday name to
give that weekday name a unique search pattern.

SuMoTuWeThFrSa

If your drop down selection was Thursday we want to look for the the first
2 characters of Thursday in the string SuMoTuWeThFrSa.

SEARCH(LEFT(A1,2),"SuMoTuWeThFrSa")
SEARCH("Th","SuMoTuWeThFrSa")

"Th" starts at character number 9 but we need to somehow make "Th"
correspond to weekday number 5. By "padding" the the string SuMoTuWeThFrSa
with a character that is guaranteed not to appear in any of the substrings
we're looking for this shifts "th" from starting character 9 to starting
character 10. We can easily convert 10 to 5 by dividing by 2.

So, using the string xSuMoTuWeThFrSa we get:

Weekday......Char...Weekday number
Sunday..........2/2.............1
Monday........4/2.............2
Tuesday........6/2.............3
Wednesday...8/2.............4
Thursday.....10/2.............5
Friday.........12/2.............6
Saturday.....14/2.............7

Thanks Biff, I'm somewhat chuffed to say that I had understood the formula
properly (even if it took me a while)

Regards,

Mike

 

[mlv]

The Lookup() function needs it's values in *ascending order*,
and this configuration leaves the cell empty when no choice
has been made in the "drop-down" cell.

Thanks for the explanation.

Regards,

Mike

 

[T. Valko]

Well, that's for anyone who might be reading this!

Thanks for the feedback!

 

[Bernd P]

Hello Biff,

Right. Thanks.

Regards,
Bernd

 

[mlv]

Well, that's for anyone who might be reading this!

Absolutely - the help and advice from this ng is excellent, but personally I
do like to understand how & why a formula works before I use it.

Thanks for the feedback!

You're welcome.