Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: If Formula


(!)

abbyt1904's Avatar
abbyt1904 abbyt1904 is offline
Computer Specs
Member with 26 posts.
THREAD STARTER
 
Join Date: Jul 2009
Experience: Intermediate
08-Aug-2012, 04:06 AM #1
Solved: If Formula
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
etaf's Avatar
etaf   (Wayne) etaf is online now
Computer Specs
Moderator with 52,945 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
08-Aug-2012, 04:30 AM #2
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.
__________________
Wayne
Please let us know what the final solution was to any problem posted
abbyt1904's Avatar
abbyt1904 abbyt1904 is offline
Computer Specs
Member with 26 posts.
THREAD STARTER
 
Join Date: Jul 2009
Experience: Intermediate
08-Aug-2012, 04:38 AM #3
Wow, thanks. The first formula works a treat.
Where you have written StartDate, what format do I enter that in as?
etaf's Avatar
etaf   (Wayne) etaf is online now
Computer Specs
Moderator with 52,945 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
08-Aug-2012, 04:43 AM #4
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
abbyt1904's Avatar
abbyt1904 abbyt1904 is offline
Computer Specs
Member with 26 posts.
THREAD STARTER
 
Join Date: Jul 2009
Experience: Intermediate
08-Aug-2012, 04:47 AM #5
Aah, brilliant. Thank you so much!
etaf's Avatar
etaf   (Wayne) etaf is online now
Computer Specs
Moderator with 52,945 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
08-Aug-2012, 04:47 AM #6
i have also used the serial number - see edit above
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑