Results 1 to 9 of 9

start of week formula

This is a discussion on start of week formula within the Excel Questions forums, part of the Question Forums category; Hello this is probably easy but I can't get my head around it. I need a formula in a cell ...

  1. #1
    Board Regular
    Join Date
    Jun 2002
    Posts
    781

    Default start of week formula

    Hello this is probably easy but I can't get my head around it.

    I need a formula in a cell that will give me the start of the current week. So if the start of the week is Monday 11th October then on Tuesday to Sunday of next week the date Monday 11th October will appear but on Monday of the following week Monday 18th October will appear and it will stay like that until the end oif the week and so on. Probably something to do with TODAY() but I have tried various little IF formulas but cannot get them to work.

    I'd appreciate any help

  2. #2
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    16,965

    Default Re: start of week formula

    Try this:

    =FLOOR(TODAY()-2,7)+2

  3. #3
    Board Regular
    Join Date
    Jun 2002
    Posts
    781

    Default Re: start of week formula

    Thank you very much. That is perfect but could you tell me how to amend it if I want the start of the week to be a Saturday.
    thank you

  4. #4
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    16,965

    Default Re: start of week formula

    =FLOOR(TODAY()-7,7)+7

  5. #5
    Board Regular
    Join Date
    Jun 2002
    Posts
    781

    Default Re: start of week formula

    thanks very much

  6. #6
    Board Regular jimrward's Avatar
    Join Date
    Feb 2003
    Location
    Sussex, Englandshire,UK
    Posts
    1,437

    Default Re: start of week formula

    try

    =A1-WEEKDAY(A1,2)+1

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: start of week formula

    Hi methody:

    Here is one way that might be of some interest ...

    ******** ******************** ************************************************************************>
    Microsoft Excel - y041009h1.xls___Running: xl97 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    ****
    2
    DayOfInterestSat*of*current*week**
    3
    Sat10/9/2004Sat*
    4
    ****
    Sheet5*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    enter the name of the day of interest in cell A3 and read the resulting date in cell B3 where the formula is ...

    =TODAY()-(WEEKDAY(TODAY())-MATCH(A3,{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0))
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  8. #8
    Board Regular
    Join Date
    Jun 2002
    Posts
    781

    Default Re: start of week formula

    yes very helpful
    thank you

  9. #9
    New Member
    Join Date
    Mar 2013
    Posts
    8

    Default Re: start of week formula

    Hi, i am trying to modify my exisiting formula that someone on this forum helped me. Hope its really simple. Here is the existing formula. ="Week "&INT((13+DAY(P2)-WEEKDAY(P2-1))/7). I want to modify it to start the week on a saturday instead of a monday.

    Much apprecaited.

    Kali



    Quote Originally Posted by Yogi Anand View Post
    Hi methody:

    Here is one way that might be of some interest ...

    ******** ******************** ************************************************************************>
    Microsoft Excel - y041009h1.xls ___Running: xl97 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =
    A
    B
    C
    D
    1
    * * * *
    2
    DayOfInterest Sat*of*current*week * *
    3
    Sat 10/9/2004 Sat *
    4
    * * * *
    Sheet5 *

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    enter the name of the day of interest in cell A3 and read the resulting date in cell B3 where the formula is ...

    =TODAY()-(WEEKDAY(TODAY())-MATCH(A3,{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0))

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com