Help in Excel 2002 Nested if function

[Marlene]

I musts write a nested if function which will translate a numeric valu
into a day of the week.

Day
1=Mon
2=Tue
3=Wed
4=Thu
5=Fri
6=Sat
7=Sun

I tried =if(a2=1,mon, (if @a2=2, tue, if(a2=3, wed, if(a2=4, thu
If(a2=5, fri, if(a2=6, sat = if(a2=7, sun, "invalid")))))))

Couldn't solve and was not able to change the numbers into day.
Please Help

 

[ryanb.]

have you considered using a lookup table?

then you could use VLOOKUP to bring in the values you need

say the numbers 1- 7 are in cells:

d1:d7

and the Mon, Tues,..., Sun are in e1:e7

then if you type "2" in cell A2

and put this in B2: =VLOOKUP(A2,D1:E7,2,FALSE) it will return TUES in B2

HTH,

ryanb.

 

[J.E. McGimpsey]

try:

=IF(A2<=7,CHOOSE(A2,"Mon","Tue","Wed","Thu","Fri","Sat","Sun"),
"invalid")

or

=IF(A2<=7,MID("MonTueWedThuFriSatSun",A2*3-2,3),"invalid")

or

=IF(A2<=7,TEXT(A2+"11/30/2003","ddd"),"invalid")

 

[Peo Sjoblom]

One way

=IF(A2="","",LOOKUP(A2,{1;2;3;4;5;6;7},{"Mon";"Tue";"Wed";"Thu";"Fri";"Sat";
"Sun"}))

 

[ryanb.]

I forgot to lock in the lookup table, use this formula instead:

put this in B2: =VLOOKUP(A2,$D$1:$E$7,2,FALSE)

will help when copying down.

have you considered using a lookup table?

then you could use VLOOKUP to bring in the values you need

say the numbers 1- 7 are in cells:

d1:d7

and the Mon, Tues,..., Sun are in e1:e7

then if you type "2" in cell A2

and put this in B2: =VLOOKUP(A2,D1:E7,2,FALSE) it will return TUES in B2

HTH,

ryanb.

 

[Arvi Laanemets]

Hi

=IF(ISERROR(MATCH(A2,{1,2,3,4,5,6,7})),"invalid",INDEX({"Monday","Tuesday","
Wednesday","Thursday","Friday","Saturday","Sunday"},MATCH(A2,{1,2,3,4,5,6,7}
)))

 

[Don Guillett]

Although CHOOSE is the way to go you will still have to use
" " surrounding your text such as "Mon"

 

[Don Guillett]

Here's another that is different. Where k14 has a number 1-7

=TEXT(TODAY()+CHOOSE(K14,5,6,7,1,2,3,4),"ddd")

 

[Don Guillett]

Don't think this will work tomorrow.
--
Don Guillett
SalesAid Software
(e-mail address removed)

Here's another that is different. Where k14 has a number 1-7

=TEXT(TODAY()+CHOOSE(K14,5,6,7,1,2,3,4),"ddd")

 

[Arvi Laanemets]

Hi

Here's another that is different. Where k14 has a number 1-7

=TEXT(TODAY()+CHOOSE(K14,5,6,7,1,2,3,4),"ddd")

Why to use CHOOSE() there?
=TEXT(TODAY()+K14,"ddd")
is exactly the same )

But a little change does wonders:
=TEXT(TODAY()+K14-WEEKDAY(TODAY(),2),"ddd")

 

[Don Guillett]

Arvi. Good...
I knew there was a good way to do this using today

 

[J.E. McGimpsey]

Downside is it makes the function unnecessarily volatile.

 

[Arvi Laanemets]

Hi all

A good job have doing we all here, working hard over simple task ))

=TEXT(A2+1,"ddd")

Maybe we need some basic lessons again? LOL

 

[J.E. McGimpsey]

Doesn't work for 1904 date system.