I have run out of arguments for nested IF statement

[christopherp]

Hi Guys,

For the majority of my Uni assignments the due date is given as a wee
number (eg assignment given week 5 - assignment due week 12)

I am trying to devise a way of displaying the current week number o
the semester. I will then use this result for a number of othe
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 neste
IF arguments. Can anyone help me shorten the formula or suggest a
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)),"Wee
12",IF((TODAY()>=($F$3+77)),"Week 11",IF((TODAY()>=($F$3+77)),"Wee
11",IF((TODAY()>=($F$3+70)),"Week 10",IF((TODAY()>=($F$3+63)),"Wee
9",IF((TODAY()>=($F$3+56)),"Week 8",IF((TODAY()>=($F$3+56)),"Wee
8",IF((TODAY()>=($F$3+49)),"Week 7",IF((TODAY()>=($F$3+42)),"Wee
6",IF((TODAY()>=($F$3+35)),"Week 5",IF((TODAY()>=($F$3+28)),"Wee
4",IF((TODAY()>=($F$3+21)),"Week 3",IF((TODAY()>=($F$3+14)),"Wee
2","not at uni")

 

[flummi]

You could use this type of formula:

15.03.2006 Week 12 <-- ="Week "&WEEKNUM(A1,2)

Hans

 

[christopherp]

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 understan
.

the Weeknum formula returns a value based on the week of the year doe
it not?

I need am trying to return the week of the semester, which started o
27th Feb 2006.

Chri

 

[flummi]

Sorry, more accurately:

A1: =today()
F3: start date of semester

B1: =WEEKNUM(a1,2)-WEEKNUM($F$3,2)+1

Regards

Hans

 

[flummi]

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

 

[Ron Rosenfeld]

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