# Solved: If Formula

Discussion in 'Business Applications' started by abbyt1904, Aug 8, 2012.

Not open for further replies.

Joined:
Jul 15, 2009
Messages:
29
Hi

I am trying to create an IF which I think should be easy...

I have a list of dates and want to say if the date is between 03/01/2011 and 09/01/2011 return "week 1", if the date is between 10/01/2011 and 16/01/2011 return "week 2", so I have a list of dates and it shows which week in the year it relates to.

Can anyone help?
Thanks
Abby

3. ### etaf Wayne Moderator

Joined:
Oct 2, 2003
Messages:
62,546
thats quite a long IF statement if you are going to do 52 weeks

=TRUNC(((A1-DATE(YEAR(A1),1,0))+6)/7)

where A1 is the date cell
and assumes 1st Jan to be week 1

if you want a different date as the weekone

Week Number From Date

=TRUNC(((A1-StartDate)+6)/7)+(WEEKDAY(A1)=WEEKDAY(StartDate))

where A1 is the date whose week number is to be calculated, and StartDate specifies the first day of Week 1.

Joined:
Jul 15, 2009
Messages:
29
Wow, thanks. The first formula works a treat.
Where you have written StartDate, what format do I enter that in as?

5. ### etaf Wayne Moderator

Joined:
Oct 2, 2003
Messages:
62,546
I entered into another cell and used that as the reference
if you need to copy down the sheet - then for startdate use \$ in front of the column and row reference and it will stay as the reference

=TRUNC(((A1-\$L\$1)+6)/7)+(WEEKDAY(A1)=WEEKDAY(\$L\$1))

or you can use the serial number for the date
3/1/2012 = 40911

=TRUNC(((A1-40911)+6)/7)+(WEEKDAY(A1)=WEEKDAY(40911))

seems to work - but worth testing fully

Joined:
Jul 15, 2009
Messages:
29
Aah, brilliant. Thank you so much!

7. ### etaf Wayne Moderator

Joined:
Oct 2, 2003
Messages:
62,546
i have also used the serial number - see edit above

As Seen On