What are the alternatives ???

[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")

 

[kghexce]

This will tell you how many weeks have passed from the Semester startin
date ($F$3) until 'today'...

=TRUNC((((Now()-$F$3)-DATE(YEAR((Now()-$F$3)),1,1))/7))+1+IF(WEEKDAY(DATE(YEAR((Now()-$F$3)),1,1))>WEEKDAY((Now()-$F$3)),1,0

 

[christopherp]

This will tell you how many weeks have passed from the Semester startin
date ($F$3) until 'today'...

=TRUNC((((Now()-$F$3)-DATE(YEAR((Now()-$F$3)),1,1))/7))+1+IF(WEEKDAY(DATE(YEAR((Now()-$F$3)),1,1))>WEEKDAY((Now()-$F$3)),1,0)

Thank you mate but it does not seem to work quite right.

I copied that formula into my sheet and it returned 12. Week 4 start
tommorow (20/03/06) so by rights I would have expected it to return 3.

The semester started on 27th Feb 06 and that is the date in cell $F$3.

Thanks agai

 

[kghexce]

When I enter the date Feb 27 into cell F3 it returns "3".

To double check...
1) Recaptured the formula from your reply to my email.
2) Pasted into a cell in the spreadsheet in another location
[To paste, switched first to formula view (CTRL `), pasted, switche
back (CTRL `)]
3) Week "3" is returned.

Think it is OK. Not sure why it isn't working for you

 

[christopherp]

The simple things in life are often best and that was the case with thi
formula:


=WEEKNUM(today(),2)-WEEKNUM($F$3,2)+1

Thank you to Hans who posted to my original post which had a differen
title

Cheers

Chri