Help with dates please

jedibrown

Board Regular
Joined
Oct 17, 2011
Messages
136
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 :)
 

Some videos you may like

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
Joined
Apr 21, 2004
Messages
7,896
Something along the lines of

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

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
This should work out for you:

=A1-WEEKDAY(A1,15)+1
 

MartinL

Well-known Member
Joined
Oct 16, 2008
Messages
1,110
Office Version
  1. 365
Platform
  1. Windows
does this do it

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

jedibrown

Board Regular
Joined
Oct 17, 2011
Messages
136

ADVERTISEMENT

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

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763

ADVERTISEMENT

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

jedibrown

Board Regular
Joined
Oct 17, 2011
Messages
136
I have a solution which works now but thanks all for your help! Much appreciated!!
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Nov 10, 2009
Messages
4,763
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
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top