start of week formula

methody

Well-known Member
Joined
Jun 17, 2002
Messages
857
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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
 
Upvote 0
try

=A1-WEEKDAY(A1,2)+1
 
Upvote 0
Hi methody:

Here is one way that might be of some interest ...
y041009h1.xls
ABCD
1
2DayOfInterestSatofcurrentweek
3Sat10/9/2004Sat
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))
 
Upvote 0
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>
Microsoft Excel - y041009h1.xls___Running: xl97 : OS = Windows XP

<tbody>
</tbody>
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout<form name="formCb059465"><input name="btCb290334" value="Copy Formula" type="button" *******='window.clipboardData.setData("Text",document.formFb543420.sltNb142651.value);'></form>

<tbody>
</tbody>
<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>=<input name="txbFb965317" value='=A3&" of current week"' size="80">

<tbody>
<form name="formFb543420">
</form>
</tbody>
<center>A</center><center>B</center><center>C</center><center>D</center>
<center>1</center>****
<center>2</center>DayOfInterestSat*of*current*week**
<center>3</center>Sat10/9/2004Sat*
<center>4</center>****
Sheet5*

<tbody>
</tbody>

<tbody>
</tbody>

[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))
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top