What are the alternatives ???
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=524026 |
What are the alternatives ???
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))WEEKDAY((Now()-$F$3)),1,0) -- kghexce ------------------------------------------------------------------------ kghexce's Profile: http://www.excelforum.com/member.php...o&userid=29804 View this thread: http://www.excelforum.com/showthread...hreadid=524026 |
What are the alternatives ???
kghexce Wrote: 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))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: http://www.excelforum.com/member.php...fo&userid=4162 View this thread: http://www.excelforum.com/showthread...hreadid=524026 |
What are the alternatives ???
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, switched back (CTRL `)] 3) Week "3" is returned. Think it is OK. Not sure why it isn't working for you. -- kghexce ------------------------------------------------------------------------ kghexce's Profile: http://www.excelforum.com/member.php...o&userid=29804 View this thread: http://www.excelforum.com/showthread...hreadid=524026 |
What are the alternatives ???
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: http://www.excelforum.com/member.php...fo&userid=4162 View this thread: http://www.excelforum.com/showthread...hreadid=524026 |
All times are GMT +1. The time now is 09:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com