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

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Something along the lines of

=IF(WEEKDAY(TODAY())=6,TODAY(),IF(WEEKDAY(TODAY())<6,TODAY()-WEEKDAY(TODAY())-1,TODAY()-1))
 
Upvote 0
This should work out for you:

=A1-WEEKDAY(A1,15)+1
 
Upvote 0
does this do it

Code:
=IF(WEEKDAY(TODAY())<>6,TODAY()-WEEKDAY(TODAY())-1)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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