software

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()gt;=($F$3 91)),quot;Week 13quot;,IF((TODAY()gt;=($F$3 84)),quot;Week
12quot;,IF((TODAY()gt;=($F$3 77)),quot;Week 11quot;,IF((TODAY()gt;=($F$3 77)),quot;Week
11quot;,IF((TODAY()gt;=($F$3 70)),quot;Week 10quot;,IF((TODAY()gt;=($F$3 63)),quot;Week
9quot;,IF((TODAY()gt;=($F$3 56)),quot;Week 8quot;,IF((TODAY()gt;=($F$3 56)),quot;Week
8quot;,IF((TODAY()gt;=($F$3 49)),quot;Week 7quot;,IF((TODAY()gt;=($F$3 42)),quot;Week
6quot;,IF((TODAY()gt;=($F$3 35)),quot;Week 5quot;,IF((TODAY()gt;=($F$3 28)),quot;Week
4quot;,IF((TODAY()gt;=($F$3 21)),quot;Week 3quot;,IF((TODAY()gt;=($F$3 14)),quot;Week
2quot;,quot;not at uniquot;))--
christopherp
------------------------------------------------------------------------
christopherp's Profile: www.excelforum.com/member.php...foamp;userid=4162
View this thread: www.excelforum.com/showthread...hreadid=524026
This will tell you how many weeks have passed from the Semester starting
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))gt;WEEKDAY((Now()-$F$3)),1,0)--
kghexce
------------------------------------------------------------------------
kghexce's Profile: www.excelforum.com/member.php...oamp;userid=29804
View this thread: www.excelforum.com/showthread...hreadid=524026
kghexce Wrote:
gt; This will tell you how many weeks have passed from the Semester starting
gt; date ($F$3) until 'today'...
gt;
gt; =TRUNC((((Now()-$F$3)-DATE(YEAR((Now()-$F$3)),1,1))/7)) 1 IF(WEEKDAY(DATE(YEAR((Now()-$F$3)),1,1))gt;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 starts
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 again--
christopherp
------------------------------------------------------------------------
christopherp's Profile: www.excelforum.com/member.php...foamp;userid=4162
View this thread: www.excelforum.com/showthread...hreadid=524026
When I enter the date Feb 27 into cell F3 it returns quot;3quot;.

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, switched
back (CTRL `)]
3) Week quot;3quot; is returned.

Think it is OK. Not sure why it isn't working for you.--
kghexce
------------------------------------------------------------------------
kghexce's Profile: www.excelforum.com/member.php...oamp;userid=29804
View this thread: www.excelforum.com/showthread...hreadid=524026
The simple things in life are often best and that was the case with this
formula:=WEEKNUM(today(),2)-WEEKNUM($F$3,2) 1

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

Cheers

Chris--
christopherp
------------------------------------------------------------------------
christopherp's Profile: www.excelforum.com/member.php...foamp;userid=4162
View this thread: www.excelforum.com/showthread...hreadid=524026