+ Reply to Thread
Results 1 to 8 of 8

Week number from custom date

  1. #1
    Registered User
    Join Date
    03-18-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Week number from custom date

    Hi there

    I have looked at some examples of Date formulas but still need help.

    I would like a formula to work out the week of semester.

    I have a list of dates in Column A and then in Column D I am trying to get a formula to look at the date in A and then calculate a week number of Semester. eg Week One, week Two etc.

    I was thinking of having the Semester start date 04 March 2013 in a cell that is referenced by the formula. Not sure if a formula could also include a end date of Semester eg 28 June 2013

    Any help would be appreciated

  2. #2
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: Week number from custom date

    ="Week " &INT((NOW()-A1)/7) assumes A1 is your Date

    this may get go started....HTH
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....

  3. #3
    Registered User
    Join Date
    03-18-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Week number from custom date

    Quote Originally Posted by judgeh59 View Post
    ="Week " &INT((NOW()-A1)/7) assumes A1 is your Date

    this may get go started....HTH
    Thanks, sorry if this is a dumb question, how do I change NOW to a cell reference with a daily date in it So a Formula in Column C looks at date in A2 and calculates week eg every 7 days, Week , Week 2 etc.



    A B C D
    04 Mar 2013
    09 Mar 2013 Week 1
    05 Mar 2013 Week 1
    11 mar 2013 Week 2
    18 mar 2013 Week 3

  4. #4
    Valued Forum Contributor
    Join Date
    02-05-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2013
    Posts
    571

    Re: Week number from custom date

    Like this
    Please Login or Register  to view this content.
    note : if you want A1 not change use $ in front of alphabet and/or number
    Last edited by SDCh; 03-18-2013 at 10:40 PM.
    Click (*) if you received helpful response.

    Regards,
    David

  5. #5
    Registered User
    Join Date
    03-18-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Week number from custom date

    Wows thanks so much.... almost perfect
    its lists Week 1 as Week 0

    but I can change the start date to a week earlier ... unless there is an easy fix.

  6. #6
    Registered User
    Join Date
    12-16-2012
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    66

    Re: Week number from custom date

    Quote Originally Posted by cferart View Post
    Wows thanks so much.... almost perfect
    its lists Week 1 as Week 0

    but I can change the start date to a week earlier ... unless there is an easy fix.
    That should work. Just adds one to the total!

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    03-18-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Week number from custom date

    Quote Originally Posted by jkray622 View Post
    That should work. Just adds one to the total!

    Please Login or Register  to view this content.
    Yay this is brilliant thanks everyone

    (not sure how to mark solved)

  8. #8
    Valued Forum Contributor
    Join Date
    02-05-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2013
    Posts
    571

    Re: Week number from custom date

    For mark solved:

    Click Thread Tools above your first post, select "Mark your thread as Solved".
    Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.
    If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1