problem with date formula
 

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,"T u",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,"T u",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.

problem with date formula
 

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"))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"davegb" wrote:

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,"T u",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,"T u",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.

problem with date formula
 

Maybe

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

Mike

"davegb" wrote:

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,"T u",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,"T u",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.

problem with date formula
 

=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"}))
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"davegb" wrote:

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,"T u",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,"T u",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.

problem with date formula
 

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

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

--
Rick (MVP - Excel)


"davegb" wrote in message
...
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,"T u",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,"T u",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.

problem with date formula
 

On Mon, 15 Dec 2008 11:26:31 -0700, davegb wrote:

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,"T u",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,"T u",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.