# 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. ## 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. ## Re: start of week formula

Try this:

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

3. ## 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. ## Re: start of week formula

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

5. ## Re: start of week formula

thanks very much

6. ## Re: start of week formula

try

=A1-WEEKDAY(A1,2)+1

7. ## 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
 B2B3C3 =

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))

thank you

9. ## 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

Originally Posted by Yogi Anand
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
 B2B3C3 =
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))

#### Posting Permissions

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