Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I have run out of arguments for nested IF statement
Hi Guys, For the majority of my Uni assignments the due date is given as a week number (eg assignment given week 5 - assignment due week 12) I am trying to devise a way of displaying the current week number of the semester. I will then use this result for a number of other procedures (cond formatting etc...) The date in cell $F$3 is the first day of the semester. I have put together a formula which works fine until I reach 7 nested IF arguments. Can anyone help me shorten the formula or suggest an alternative way to do what I would like to do: Please see below for formula Thanks in advance :) Chris =IF((TODAY()=($F$3+91)),"Week 13",IF((TODAY()=($F$3+84)),"Week 12",IF((TODAY()=($F$3+77)),"Week 11",IF((TODAY()=($F$3+77)),"Week 11",IF((TODAY()=($F$3+70)),"Week 10",IF((TODAY()=($F$3+63)),"Week 9",IF((TODAY()=($F$3+56)),"Week 8",IF((TODAY()=($F$3+56)),"Week 8",IF((TODAY()=($F$3+49)),"Week 7",IF((TODAY()=($F$3+42)),"Week 6",IF((TODAY()=($F$3+35)),"Week 5",IF((TODAY()=($F$3+28)),"Week 4",IF((TODAY()=($F$3+21)),"Week 3",IF((TODAY()=($F$3+14)),"Week 2","not at uni")) -- christopherp ------------------------------------------------------------------------ christopherp's Profile: http://www.excelforum.com/member.php...fo&userid=4162 View this thread: http://www.excelforum.com/showthread...hreadid=524020 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
I have run out of arguments for nested IF statement
You could use this type of formula:
15.03.2006 Week 12 <-- ="Week "&WEEKNUM(A1,2) Hans |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
I have run out of arguments for nested IF statement
flummi Wrote: You could use this type of formula: 15.03.2006 Week 12 <-- ="Week "&WEEKNUM(A1,2) Hans Thank you for the suggestion Hans but I do not quite understand . the Weeknum formula returns a value based on the week of the year does it not? I need am trying to return the week of the semester, which started on 27th Feb 2006. Chris -- christopherp ------------------------------------------------------------------------ christopherp's Profile: http://www.excelforum.com/member.php...fo&userid=4162 View this thread: http://www.excelforum.com/showthread...hreadid=524020 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
I have run out of arguments for nested IF statement
Sorry, more accurately:
A1: =today() F3: start date of semester B1: =WEEKNUM(a1,2)-WEEKNUM($F$3,2)+1 Regards Hans |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
I have run out of arguments for nested IF statement
Well, the idea is that the calender week of the current date minus the
calender week of the semester's start date plus 1 would give you the relative week in the semester i.e. Something along these lines: sem start cal week sem week 27.02.2006 10 1 current dates 06.03.2006 11 2 <-- =WEEKNUM(A5;2)-WEEKNUM($A$2;2)+1 13.03.2006 12 3 20.03.2006 13 4 27.03.2006 14 5 03.04.2006 15 6 Hans |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
I have run out of arguments for nested IF statement
On Sun, 19 Mar 2006 04:13:34 -0600, christopherp
wrote: Hi Guys, For the majority of my Uni assignments the due date is given as a week number (eg assignment given week 5 - assignment due week 12) I am trying to devise a way of displaying the current week number of the semester. I will then use this result for a number of other procedures (cond formatting etc...) The date in cell $F$3 is the first day of the semester. I have put together a formula which works fine until I reach 7 nested IF arguments. Can anyone help me shorten the formula or suggest an alternative way to do what I would like to do: Please see below for formula Thanks in advance :) Chris =IF((TODAY()=($F$3+91)),"Week 13",IF((TODAY()=($F$3+84)),"Week 12",IF((TODAY()=($F$3+77)),"Week 11",IF((TODAY()=($F$3+77)),"Week 11",IF((TODAY()=($F$3+70)),"Week 10",IF((TODAY()=($F$3+63)),"Week 9",IF((TODAY()=($F$3+56)),"Week 8",IF((TODAY()=($F$3+56)),"Week 8",IF((TODAY()=($F$3+49)),"Week 7",IF((TODAY()=($F$3+42)),"Week 6",IF((TODAY()=($F$3+35)),"Week 5",IF((TODAY()=($F$3+28)),"Week 4",IF((TODAY()=($F$3+21)),"Week 3",IF((TODAY()=($F$3+14)),"Week 2","not at uni")) Well, the formula =INT((today()-$F$3)/7)+1 will give you the current semester's week number. So maybe something like: ="Week " & INT((TODAY()-$F$3)/7)+1 --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If statement that contains 6+ arguments in excel | Excel Worksheet Functions | |||
SET statement tutorial | Excel Discussion (Misc queries) | |||
If statement | Excel Discussion (Misc queries) | |||
Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions | |||
IF statement w/ more than 7 arguments needed | Excel Worksheet Functions |