more than 7 if statements 2

[smiley0q0]

i have had more than 7 if statements in one formula before, but for some reason, i can't figure this one out, probably because this one is such a simple one i'm missing the obvious.
my cell of reference is going to be a month. if it is Jan i want it to do something, if it is Feb, i want it to do something else etc.

i have been trying something simple like

=if(a1="Jan","1",if(a1="Feb","2",if(a1="Mar","3", etc...

but those are regular nested if statements and in order to have more than 7, i need to have nested if statements inside of nested if statements.
here is one i made to have a row of dates to show only the weekdays, and then the last day of the month even if it was a weekend.

=IF(U13="","",IF(U13=EOMONTH(U13,0),"",(IF(IF(WEEKDAY(U13)=6,U13+3,U13+1)>=EOMONTH(U13,0),EOMONTH(U13,0),(IF(VLOOKUP(IF(WEEKDAY(U13)=6,U13+3,U13+1),Qry550!$A$2:$A$300,1)=(IF(WEEKDAY(U13)=6,U13+3,U13+1)),(IF(WEEKDAY(U13)=6,U13+3,U13+1)),(IF(WEEKDAY(U13)=6,U13+13,U13+1))))))))

if any of you have any ideas on how to do this, i'd really appreciate it.

Thank you,
Smiley B-)

 

[dsi]

You may want to look into creating a custom function. This would be much easier than lengthy forumlas.

 

[DaleWatson123321]

Smiley,

If your problem is only with the FIRST formula you listed, one solution would be as follows:

Create a simple table - off to the side or on a separate sheet. This table would have two columns - one for the Month and the other having numbers 1 through 12 - i.e. 1 opposite "Jan", 2 opposite "Feb", etc. I'd also recommend assigning a Range Name to the table - e.g. call it "months".

Then use the following formula, which will produce the value representing the month:

=VLOOKUP(A1,months,2)

I hope this is the "obvious" solution you were looking for.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[smiley0q0]

how do i creat a custom function? I've never even heard of that before, but it sounds like something i will greatly enjoy and be able to do more with less. which is always good.

Smiley B-)

 

[dsi]

Smiley:

You will need to know a little about VBA. Here is a sample for you.

Hit Alt-F11 (Tools>Macros>VB Editor)
Select Insert>Module
Copy and Paste this code into the module:

Public Function SampleFunction(sMonth As String) As Integer
    Select Case sMonth
        Case "Jan": SampleFunction = 1
        Case "Feb": SampleFunction = 2
        Case "Mar": SampleFunction = 3
        Case "Apr": SampleFunction = 4
        Case "May": SampleFunction = 5
        Case Else: SampleFunction = 0
    End Select
End Function

- Close the VB Editor or switch back to the worksheet.
- In cell A2, select Insert > Function
- In the list, pick User Defined and select SampleFunction
- You will be prompted to enter the arguments
- Hit the little button to the right of the textbox
- Select cell A1 and hit return
- Hit OK
- Now, type "Feb" in cell A1. Cell A2 should now be 2.

This sample is not worthy of a custom function because the solution proposed by DaleWatson above would do the trick. This method is typicall used for more complicated routines.

In your example, you can pass the date as the argument. You can perform multiple tests on the date to return the value you choose. If you have specific questions about VBA code, just post them in the VBA forum. There are plenty of experts there that can help you work through this.

 

[smiley0q0]

Thank you very much, i tried it out and it works great

 

[DaleWatson123321]

Smiley,

Its not clear from your last posting which option you used. It would be interesting to know. Can you share that with use ? Thanks.

In the meantime, I want to thank "dsi" ...with a STAR. I too am relatively new to VBA, so I appreciate the custom function example he provided.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[smiley0q0]

i used dsi's case statement function. i had never seen that before and wanted to try it out. once i read your vlookup table, i realised i should have thought of that because i had just done something very similar a few weeks ago.

Than you again for your help.

Smiley B-)