+ Reply to Thread
Results 1 to 5 of 5

Split each month into weeks ie Jan 2014 Week 1/2/3/4

  1. #1
    Registered User
    Join Date
    02-26-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    12

    Split each month into weeks ie Jan 2014 Week 1/2/3/4

    Hi All

    What i need to do is, based on a date, to split each of the months of the year 2014 into weeks.
    It`s ok if we consider that each month has 4 weeks only.
    Or if easier, ok to split the month in the exact number of weeks they had so far.

    Example:

    Date: 1/01/2014 week 1 ; 6/01/2014 week 2
    Looking at March 2014 for example : 1st March was on a SAT and 2nd on SUN. It would be best to consider as week 1 from 1st March to 9th March.

    I have attached a worksheet in case anyone is able to help me.

    Many thanks in advance,
    Ana
    Attached Files Attached Files

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Split each month into weeks ie Jan 2014 Week 1/2/3/4

    Put in B2 and copied down:

    =WEEKNUM(A2)-WEEKNUM(A2-DAY(A2)+1)+1

  3. #3
    Registered User
    Join Date
    02-26-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Split each month into weeks ie Jan 2014 Week 1/2/3/4

    Thanks Azumi

    Looks alright, although i would like the week to start on Monday not Sunday. How can i make this work?

    Also i noticed that if the month has the first few days FRI/SAT/SUN this is calculated as WEEK 1. I would rather have FRI/SAT/SUN /MON....to following SUN calculated as week 1.

    Thanks!

  4. #4
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Split each month into weeks ie Jan 2014 Week 1/2/3/4

    Start Monday :

    =INT((DAY(A2)+(WEEKDAY(DATE(YEAR(A2);MONTH(A2);1);2))-2)/7)+1

    Hope it works

  5. #5
    Registered User
    Join Date
    02-26-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Split each month into weeks ie Jan 2014 Week 1/2/3/4

    It works just fine..that is great. Appreciate your help.

    Would love to understand that you did there..what is the logic behind it?

+ 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. [SOLVED] Macro that displays all the weeks between Start Week and End Week (ends in next year)
    By Tona in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-31-2014, 05:38 AM
  2. [SOLVED] Showing Fridays of each month in a 4 week month and a five week month
    By david_j_p in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-09-2013, 06:27 AM
  3. [SOLVED] Split Month to Date data into Weeks
    By dksodhi in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-15-2013, 10:59 PM
  4. Calculating weeks/days old if child is less than 1 month or 1 week old.
    By sharpmel in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-17-2013, 04:00 PM
  5. Replies: 10
    Last Post: 01-22-2012, 10:05 AM

Tags for this Thread

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