Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
christopherp
 
Posts: n/a
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.misc
kghexce
 
Posts: n/a
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.misc
christopherp
 
Posts: n/a
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.misc
kghexce
 
Posts: n/a
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.misc
christopherp
 
Posts: n/a
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct doesn't work with columns... alternatives? qwopzxnm Excel Worksheet Functions 4 February 17th 06 11:36 PM
Nested IF Function and VLookup Alternatives robert.holmes Excel Worksheet Functions 1 December 12th 05 11:28 PM
Alternatives to Excel Spider Excel Discussion (Misc queries) 2 March 1st 05 02:03 PM
Pocket Excel Alternatives? MB7 Excel Discussion (Misc queries) 0 February 16th 05 05:03 PM


All times are GMT +1. The time now is 04:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"