#### jedibrown

##### Board Regular
Hi, I was just wondering whether there was a formula to change a date once per week.

For example, instead of using =today() is there a way that I can make the date stay on a Friday until the following one.

So tomorrow it would change to 16/12/11 and stay like that until next Friday when it would change to 23/12/11 and so on.

This is because I am trying to put in "week beginning" but my week begins on a Friday. Thanks

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### SteveO59L

##### Well-known Member
Something along the lines of

=IF(WEEKDAY(TODAY())=6,TODAY(),IF(WEEKDAY(TODAY())<6,TODAY()-WEEKDAY(TODAY())-1,TODAY()-1))

#### MrKowz

##### Well-known Member
This should work out for you:

=A1-WEEKDAY(A1,15)+1

#### MartinL

##### Well-known Member
does this do it

Code:
``=IF(WEEKDAY(TODAY())<>6,TODAY()-WEEKDAY(TODAY())-1)``

#### jedibrown

##### Board Regular

Perfect - Works brilliantly thanks for all of your help!!

#### BeingYogi

##### Board Regular
May Be:

=TODAY()+(WEEKDAY(TODAY())>6)*7-WEEKDAY(TODAY())-1

#### p45cal

##### Well-known Member

=today()-mod(today()+1,7)

#### jedibrown

##### Board Regular
I have a solution which works now but thanks all for your help! Much appreciated!!

#### MrKowz

##### Well-known Member
I have a solution which works now but thanks all for your help! Much appreciated!!

Glad we were able to find a solution. Thank you for the feedback!

#### p45cal

##### Well-known Member
This should work out for you:

=A1-WEEKDAY(A1,15)+1
MrKowz, is there something different about the weekday function in Excel versions 2007 up? (Your formula in xl2003 in column B):
Excel Workbook
ABC
1Tue 13 Dec 2011#NUM!Fri 09 Dec 2011
2Wed 14 Dec 2011#NUM!Fri 09 Dec 2011
3Thu 15 Dec 2011#NUM!Fri 09 Dec 2011
4Fri 16 Dec 2011#NUM!Fri 16 Dec 2011
5Sat 17 Dec 2011#NUM!Fri 16 Dec 2011
6Sun 18 Dec 2011#NUM!Fri 16 Dec 2011
7Mon 19 Dec 2011#NUM!Fri 16 Dec 2011
8Tue 20 Dec 2011#NUM!Fri 16 Dec 2011
9Wed 21 Dec 2011#NUM!Fri 16 Dec 2011
10Thu 22 Dec 2011#NUM!Fri 16 Dec 2011
11Fri 23 Dec 2011#NUM!Fri 23 Dec 2011
Sheet

Replies
0
Views
57
Replies
2
Views
120
Replies
3
Views
189
Replies
5
Views
77
Replies
1
Views
130

1,127,204
Messages
5,623,354
Members
415,969
Latest member
Rey99

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

### Which adblocker are you using?

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

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