Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
DJ Dusty
 
Posts: n/a
Default Show a date based on today


I would like a cell to be able to show a week ending date (a Friday)
based on today's date - I assume the TODAY() function would be
involved, but just not sure how.

So:

If today's date is 11/11/2004 (Thurs), then I would like the cell to
show 12/11/2004 (Friday).

If today's date is 12/11/2004 (Fri), then it should still show as
12/11/2004 (Fri).

If today's date is 13/11/2004 (Sat), then it should show the next
Friday date of 19/11/2004.

I'd be grateful for the answer - please!!!


--
DJ Dusty
------------------------------------------------------------------------
DJ Dusty's Profile: http://www.excelforum.com/member.php...o&userid=16335
View this thread: http://www.excelforum.com/showthread...hreadid=277549

  #2   Report Post  
duane
 
Posts: n/a
Default


i would set up a table and name it "table" as follows

row number col 1 col2
1 1 5
2 2 4
3 3 3
4 4 2
5 5 1
6 6 0
7 7 6

and then if a10 is the date you start with

=A10+VLOOKUP(WEEKDAY(A10,1),table,2)


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=277549

  #3   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Thu, 11 Nov 2004 15:47:13 -0600, DJ Dusty
wrote:


I would like a cell to be able to show a week ending date (a Friday)
based on today's date - I assume the TODAY() function would be
involved, but just not sure how.

So:

If today's date is 11/11/2004 (Thurs), then I would like the cell to
show 12/11/2004 (Friday).

If today's date is 12/11/2004 (Fri), then it should still show as
12/11/2004 (Fri).

If today's date is 13/11/2004 (Sat), then it should show the next
Friday date of 19/11/2004.

I'd be grateful for the answer - please!!!


The "general" formula would be:

=A1-WEEKDAY(A1+1)+7

So if you want it based on today, just substitute TODAY() for A1:

=TODAY()-WEEKDAY(TODAY()+1)+7


--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I stop today() from updating date on saved spreadsheets? lionmark Excel Discussion (Misc queries) 2 January 4th 05 02:03 AM
Today() or Date() function help julisimo Excel Discussion (Misc queries) 8 January 3rd 05 05:19 PM
Addition to Turn cell red if today is greater or equal to date in cell Rich New Users to Excel 2 December 9th 04 03:06 AM
Turn cell red if today is greater or equal to date in cell Rich New Users to Excel 2 December 7th 04 11:50 PM
Hiding rows based on date Steve Excel Worksheet Functions 2 November 1st 04 03:30 PM


All times are GMT +1. The time now is 09:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"