Identifying Weekdays

M

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,
 
R

RagDyer

Does this work for you:

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

Dave Peterson

You could use a formula like:

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

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

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

T. Valko

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

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

mlv

RagDyer said:
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
 
M

mlv

Dave said:
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,
 
M

mlv

T. Valko said:
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.
 
D

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

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
 
R

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 !
-----------------------------------------------------------------------------------------------

RagDyer said:
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
 
M

mlv

T. Valko said:
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
 
M

mlv

RagDyeR said:
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
 
M

mlv

T. Valko said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top