problem with date formula

[davegb]

I have been using this formula in a spreadsheet successfully for a while:
=IF(WEEKDAY(A5,1)=7,"Sa",IF(WEEKDAY(A5,1)=6,"F",IF(WEEKDAY(A5,1)=5,
_"Th",IF(WEEKDAY(A5,1)=4,"W",IF(WEEKDAY(A5,1)=3,"Tu",IF(WEEKDAY(A5,1)=2, _
"M",IF(WEEKDAY(A5,1)=1,"Su","")))))))

In some circumstances, it gives a #VALUE error. So I tried to replace it
with:

=if(iserrror(IF(WEEKDAY(A5,1)=7,"Sa",IF(WEEKDAY(A5,1)=6,"F",IF(WEEKDAY(A5,1)=5,
_"Th",IF(WEEKDAY(A5,1)=4,"W",IF(WEEKDAY(A5,1)=3,"Tu",IF(WEEKDAY(A5,1)=2,"M",
_IF(WEEKDAY(A5,1)=1,"Su","")))))))),"",IF(WEEKDAY(A5,1)=7,"Sa",IF(WEEKDAY(A5,1)=6,
_"F",IF(WEEKDAY(A5,1)=5,"Th",IF(WEEKDAY(A5,1)=4,"W",IF(WEEKDAY(A5,1)=3,"Tu",
_IF(WEEKDAY(A5,1)=2,"M",IF(WEEKDAY(A5,1)=1,"Su",""))))))))

I get a formula error. I use a formlaic method to do this, simply add the
IF(ISERROR(
to the beginning of the formula after I copy the original, which makes the
original formula the IF test condition, then add
),"",
and paste the original formula in, then a closing parenthesis, which puts
in the blank if the iserror is true condition and the original formula as
the false condtion. So I just blank out the error code. So why does my
method fail me here? It's highlighting the 6th occurence of "WEEKDAY" as
the probably culprit. Anyone else see what I'm missing here?

Thanks, as always.

 

[Luke M]

Excel places a limit of 7 on nested functions. Whereas your first formula
only had 7 nested IF's, your new formula has 8. (Main, plus 7 down each
path). Your error checking places you one over the limit.
This will accomplish want you want I believe.
=IF(ISERROR(CHOOSE(WEEKDAY(A10,1),"Su","M","Tu","W","Th","F","Sa")),"",CHOOSE(WEEKDAY(A10,1),"Su","M","Tu","W","Th","F","Sa"))

 

[Mike H]

Maybe

=IF(ISNUMBER(A5),CHOOSE(WEEKDAY(A5,1),"Su","M","Tu","W","Th","F","Sa"),"")

Mike

 

[Michael]

=IF(ISERROR(LOOKUP(A5,{1,2,3,4,5,6,7},{"M","Tu","W","Th","F","Sa","Su"})),"",LOOKUP(A5,{1,2,3,4,5,6,7},{"M","Tu","W","Th","F","Sa","Su"}))

 

[Rick Rothstein]

Instead of the approach you have been using, try this formula out...

=LEFT(TEXT(A5,"ddd"),2)

 

[salgud]

I have been using this formula in a spreadsheet successfully for a while:
=IF(WEEKDAY(A5,1)=7,"Sa",IF(WEEKDAY(A5,1)=6,"F",IF(WEEKDAY(A5,1)=5,
_"Th",IF(WEEKDAY(A5,1)=4,"W",IF(WEEKDAY(A5,1)=3,"Tu",IF(WEEKDAY(A5,1)=2, _
"M",IF(WEEKDAY(A5,1)=1,"Su","")))))))

In some circumstances, it gives a #VALUE error. So I tried to replace it
with:

=if(iserrror(IF(WEEKDAY(A5,1)=7,"Sa",IF(WEEKDAY(A5,1)=6,"F",IF(WEEKDAY(A5,1)=5,
_"Th",IF(WEEKDAY(A5,1)=4,"W",IF(WEEKDAY(A5,1)=3,"Tu",IF(WEEKDAY(A5,1)=2,"M",
_IF(WEEKDAY(A5,1)=1,"Su","")))))))),"",IF(WEEKDAY(A5,1)=7,"Sa",IF(WEEKDAY(A5,1)=6,
_"F",IF(WEEKDAY(A5,1)=5,"Th",IF(WEEKDAY(A5,1)=4,"W",IF(WEEKDAY(A5,1)=3,"Tu",
_IF(WEEKDAY(A5,1)=2,"M",IF(WEEKDAY(A5,1)=1,"Su",""))))))))

I get a formula error. I use a formlaic method to do this, simply add the
IF(ISERROR(
to the beginning of the formula after I copy the original, which makes the
original formula the IF test condition, then add
),"",
and paste the original formula in, then a closing parenthesis, which puts
in the blank if the iserror is true condition and the original formula as
the false condtion. So I just blank out the error code. So why does my
method fail me here? It's highlighting the 6th occurence of "WEEKDAY" as
the probably culprit. Anyone else see what I'm missing here?

Thanks, as always.

Thanks to everyone. All much simpler approach that mine. Good lesson. I
chose Rick's solution because it seemed the simplest.