start of week formula

[methody]

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

 

[Scott Huish]

Try this:

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

 

[methody]

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

 

[Scott Huish]

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

 

[methody]

thanks very much

 

[jimrward]

try

=A1-WEEKDAY(A1,2)+1

 

[Yogi Anand]

Hi methody:

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

y041009h1.xls
	A	B	C	D
1
2	DayOfInterest	Satofcurrentweek
3	Sat	10/9/2004	Sat
4
Sheet5

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

 

[methody]

yes very helpful
thank you

 

[krunk]

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

Hi methody:

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

******** ******************** ************************************************************************><center>[TABLE="align: center"]
<tbody>[TR]
[TD="bgcolor: #0c266b, colspan: 5"][TABLE="width: 100%, align: center"]
<tbody>[TR]
[TD="align: left"]Microsoft Excel - y041009h1.xls[/TD]
[TD="align: right"]___Running: xl97 : OS = Windows XP [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #d4d0c8, colspan: 5"][TABLE="width: 100%, align: center"]
<tbody>[TR]
[TD](F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout[/TD]
[TD="align: center"]<form name="formCb059465"><input name="btCb290334" value="Copy Formula" type="button" *******='window.clipboardData.setData("Text",document.formFb543420.sltNb142651.value);'></form>[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="bgcolor: white, colspan: 5"]

<tbody>
<form name="formFb543420">[TD="bgcolor: white"]<select onchange="document.formFb543420.txbFb965317.value = document.formFb543420.sltNb142651.value" name="sltNb142651"><option selected="" value='=A3&" of current week"'>B2<option value='=TODAY()-(WEEKDAY(TODAY())-MATCH(A3,{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0))'>B3<option value="=B3">C3</option></select>[/TD]
[TD="bgcolor: #d4d0c8, align: right"] = [/TD]
[TD="bgcolor: white, align: left"]<input name="txbFb965317" value='=A3&" of current week"' size="80">[/TD]
</form>
</tbody>

[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #D4D0C8"][/TD]
[TD="bgcolor: #D4D0C8"]<center>A</center>[/TD]
[TD="bgcolor: #D4D0C8"]<center>B</center>[/TD]
[TD="bgcolor: #D4D0C8"]<center>C</center>[/TD]
[TD="bgcolor: #D4D0C8"]<center>D</center>[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #D4D0C8"]<center>1</center>[/TD]
[TD="bgcolor: #FFFFFF, align: center"]*[/TD]
[TD="bgcolor: #FFFFFF, align: center"]*[/TD]
[TD="bgcolor: #FFFFFF, align: center"]*[/TD]
[TD="bgcolor: #FFFFFF, align: right"]*[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #D4D0C8"]<center>2</center>[/TD]
[TD="bgcolor: #FFFFFF, align: center"]DayOfInterest[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Sat*of*current*week[/TD]
[TD="bgcolor: #FFFFFF, align: center"]*[/TD]
[TD="bgcolor: #FFFFFF, align: right"]*[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #D4D0C8"]<center>3</center>[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Sat[/TD]
[TD="bgcolor: #FFFFFF, align: center"]10/9/2004[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Sat[/TD]
[TD="bgcolor: #FFFFFF, align: right"]*[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #D4D0C8"]<center>4</center>[/TD]
[TD="bgcolor: #FFFFFF, align: center"]*[/TD]
[TD="bgcolor: #FFFFFF, align: center"]*[/TD]
[TD="bgcolor: #FFFFFF, align: center"]*[/TD]
[TD="bgcolor: #FFFFFF, align: right"]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #D4D0C8, colspan: 5"][TABLE="width: 100%, align: left"]
<tbody>[TR]
[TD="bgcolor: #FFFFFF, align: left"]Sheet5[/TD]
[TD]*[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[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.</center>

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