| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date / formula problem | Excel Discussion (Misc queries) | |||
| Date Formula Problem - Leave date blank if Null | Excel Worksheet Functions | |||
| Problem with Formula (Date) | Excel Discussion (Misc queries) | |||
| Date formula problem | Excel Discussion (Misc queries) | |||
| Date formula problem | Excel Discussion (Misc queries) | |||
![davegb[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
