+ Reply to Thread
Results 1 to 4 of 4

Format a cell to display week number starting from a certain date

  1. #1
    Registered User
    Join Date
    09-22-2014
    Location
    Iowa City, IA
    MS-Off Ver
    2010
    Posts
    7

    Format a cell to display week number starting from a certain date

    I'm trying to format column B to display the week number (1-4) starting from a certain date and cycle to the next week every Monday using the date provided in column A. For example:

    If I start at July 11, 2011 (Monday) in column A, I want that to display "Week 1" in column B. I want this to happen for all dates up until July 18, 2011 (the following Monday), when column B should display "Week 2". I want this cycle to continue, but after it reaches "Week 4" I want it to start over and go back to "Week 1".

    Is there any way to do this? I would enter the week number manually, but unfortunately I have over 30,000 rows of data. Any help is greatly appreciated!

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Format a cell to display week number starting from a certain date

    =weeknum(a1,2)
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    09-22-2014
    Location
    Iowa City, IA
    MS-Off Ver
    2010
    Posts
    7

    Re: Format a cell to display week number starting from a certain date

    This is what I had previously, but it just returns the week number out of the year. So July 11, 2011 returns "27". I want the numbers to cycle from 1 to 4 like I described in the post.

  4. #4
    Registered User
    Join Date
    09-22-2014
    Location
    Iowa City, IA
    MS-Off Ver
    2010
    Posts
    7

    Re: Format a cell to display week number starting from a certain date

    Nevermind, I solved the issue:

    =IF(MOD(WEEKNUM(date-(startdate),1),4)>0,MOD(WEEKNUM(date-(startdate),1),4),4)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Drap color bar and triangle to display %, week number and date
    By Rocky2013 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-10-2013, 11:18 PM
  2. Convert date to week number for fiscal year starting July 25
    By ARayburn in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-15-2013, 10:07 AM
  3. Replies: 0
    Last Post: 01-21-2013, 08:46 AM
  4. Display week in cell by date - help
    By lpuname in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-13-2011, 05:35 AM
  5. format date cell with week number
    By excellentexcel in forum Excel General
    Replies: 7
    Last Post: 01-08-2009, 07:57 AM

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