Help with dates please

[jedibrown]

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

 

[SteveO59L]

Something along the lines of

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

 

[MrKowz]

This should work out for you:

=A1-WEEKDAY(A1,15)+1

 

[MartinL]

does this do it

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

 

[jedibrown]

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

 

[BeingYogi]

May Be:

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

 

[p45cal]

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

 

[jedibrown]

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

 

[MrKowz]

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]

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
	A	B	C
1	Tue 13 Dec 2011	#NUM!	Fri 09 Dec 2011
2	Wed 14 Dec 2011	#NUM!	Fri 09 Dec 2011
3	Thu 15 Dec 2011	#NUM!	Fri 09 Dec 2011
4	Fri 16 Dec 2011	#NUM!	Fri 16 Dec 2011
5	Sat 17 Dec 2011	#NUM!	Fri 16 Dec 2011
6	Sun 18 Dec 2011	#NUM!	Fri 16 Dec 2011
7	Mon 19 Dec 2011	#NUM!	Fri 16 Dec 2011
8	Tue 20 Dec 2011	#NUM!	Fri 16 Dec 2011
9	Wed 21 Dec 2011	#NUM!	Fri 16 Dec 2011
10	Thu 22 Dec 2011	#NUM!	Fri 16 Dec 2011
11	Fri 23 Dec 2011	#NUM!	Fri 23 Dec 2011
Sheet