1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: If Formula

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

Thread Status:
Not open for further replies.
Advertisement
  1. abbyt1904

    abbyt1904 Thread Starter

    Joined:
    Jul 15, 2009
    Messages:
    28
    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
     
  2. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,895
    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.
     
  3. abbyt1904

    abbyt1904 Thread Starter

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

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,895
    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
     
  5. abbyt1904

    abbyt1904 Thread Starter

    Joined:
    Jul 15, 2009
    Messages:
    28
    Aah, brilliant. Thank you so much!
     
  6. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,895
    i have also used the serial number - see edit above
     
  7. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1064281