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

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

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
:confused: .

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

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

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

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