And here is another..
=IF(WEEKDAY(A2)=6,7)+6-WEEKDAY(A2)+A2
For a more generic solution for any day of the week..
IF(WEEKDAY(Date)=DayToFind,7)+DayToFind-WEEKDAY(Date)+Date
Where the 'DayToFind' is a number from 1 to 7 where 1 = Sunday to 7 = Saturday.
Regards Robert
"Austin M. Horst" wrote in message ...
I'm trying to write a formula to return this Friday's date (Format m/d/yyyy).
If it's a Saturday, I want next weeks Friday to be displayed.
The following works, but takes 7 cells (each formula is in a different cell).
I would like to combine the following into one formula.
=IF(WEEKDAY(NOW())=1,TODAY()+5,"")
=IF(WEEKDAY(NOW())=2,TODAY()+4,"")
=IF(WEEKDAY(NOW())=3,TODAY()+3,"")
=IF(WEEKDAY(NOW())=4,TODAY()+2,"")
=IF(WEEKDAY(NOW())=5,TODAY()+1,"")
=IF(WEEKDAY(NOW())=6,TODAY()+0,"")
=IF(WEEKDAY(NOW())=7,TODAY()+6,"")
Value returned: 4/30/2004
If I join each line together with an "&" the date is retuned in decimal value, not m/d/yyyy.
The "number format" of the cell is Category: Date | Type: m/dd/yyy
(The following is all on one line in my sheet, I broke it up here to make it easier to see)
=IF(WEEKDAY(NOW())=1,TODAY()+5,"")&
IF(WEEKDAY(NOW())=2,TODAY()+4,"")&
IF(WEEKDAY(NOW())=3,TODAY()+3,"")&
IF(WEEKDAY(NOW())=4,TODAY()+2,"")&
IF(WEEKDAY(NOW())=5,TODAY()+1,"")&
IF(WEEKDAY(NOW())=6,TODAY()+0,"")&
IF(WEEKDAY(NOW())=7,TODAY()+6,"")
Value returned: 38107
Maybe there is a better way to write this.
Any ideas?
Thanks,
Austin M. Horst
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
http://www.grisoft.com).
Version: 6.0.659 / Virus Database: 423 - Release Date: 16/04/2004