+ Reply to Thread
Results 1 to 4 of 4

Need to get week commencing forumla working *Pleaaaaase help*

  1. #1
    Registered User
    Join Date
    10-15-2012
    Location
    brighton
    MS-Off Ver
    Excel 2010
    Posts
    11

    Wink Need to get week commencing forumla working *Pleaaaaase help*

    Hi guys,

    So I have a report to run and it is must show the last 4 weeks dates (week commencing a Monday always), what I've done is get a date 01/01/2013 then done a =YEAR&WEEKNUM,2 on it to get something like 20132 so i can do quick and simple IFS. The problem however is the report needs to show the last 4 weeks with the week commencing date. So...

    If i run it on 17th Jan and want to show the 4 previous weeks commencing underneath how do i do this, the answer is obviously Monday 7th Jan 2013 but don't want to type it manually? I've done a temporary fix (=17/1/2013-7days) but that isn't good enough.

    Also tried to do weekday on each day in 2013 and done a (LEFT) formula to pick up everything that starts with the first day of the month but got confused, and you probably are now as well after all of that malarkey. Example below;

    Remember week commencing needs to be the Monday;

    Report date 16/1/13

    Week Commencing
    09/01/2013 - should be 7/1/13
    02/01/2013 - should be 31/12/12
    26/12/2012 - should be 24/12/12
    19/12/2012 - should be 17/12/12

    Any help will be MASSIVELY appreciated!!!!!

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,886

    Re: Need to get week commencing forumla working *Pleaaaaase help*

    First day of current week
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-15-2012
    Location
    brighton
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Need to get week commencing forumla working *Pleaaaaase help*

    Wicked, so if i then wanted to show the last 4 weeks how would this be done? As mentioned i'd rather not do your formula and then -7.

    So in theory is it possible to do what you said (=TODAY()-WEEKDAY(TODAY(),2)+1) and then for it to show the first day of last week?

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,886

    Re: Need to get week commencing forumla working *Pleaaaaase help*

    Try =TODAY()-WEEKDAY(TODAY(),2)-27 to get the start date of your four week period

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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