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

You could use this type of formula:

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

Hans

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

Sorry, more accurately:

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

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

Regards

Hans

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

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