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/200

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/yy
(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: 3810

Maybe there is a better way to write this
Any ideas

Thanks
Austin M. Horst

=Today()+Choose(weekday(today()),5,4,3,2,1,0,6)

--
Regards,
Tom Ogilvy

"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

Austin,

Try the following formula:
=NOW()+CHOOSE(WEEKDAY(NOW(),5,4,3,2,1,0,6)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"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

My previous reply was missing a closing paren. Use

=NOW()+CHOOSE(WEEKDAY(NOW()),5,4,3,2,1,0,6)

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Chip Pearson" wrote in message
...
Austin,

Try the following formula:
=NOW()+CHOOSE(WEEKDAY(NOW(),5,4,3,2,1,0,6)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"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

If you want Friday to give the following Friday, use

=TODAY()+CHOOSE(WEEKDAY(TODAY()),5,4,3,2,1,7,6)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Tom Ogilvy" wrote in message
...
=Today()+Choose(weekday(today()),5,4,3,2,1,0,6)

--
Regards,
Tom Ogilvy

"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

Hi Chip!

You meant:
=NOW()+CHOOSE(WEEKDAY(NOW()),5,4,3,2,1,0,6)

Missing parentheses.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia


It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH.
See:
http://www.microsoft.com/security/protect/

I think the following will do what you want:


=IF(WEEKDAY(TODAY())=6,TODAY(),IF(WEEKDAY(TODAY()) =7,TODAY()+6,TODAY()+6-(WEEKDAY(TODAY()))))

I have not tested it with all dates, so you will want to check it out.

Best

John Baker


Austin M. Horst wrote:

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

Just another option:

=A1+MOD(138612,WEEKDAY(A1)+6)

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"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

Thank you all.
Each one works great!


Austin M. Horst

Dana,
Just curious but how did you derive the number 138612?
I see that it is the Key to this approach!!
TIA,


"Dana DeLouis" wrote in message
...
Just another option:

=A1+MOD(138612,WEEKDAY(A1)+6)

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"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

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

=A1+MOD(138612,WEEKDAY(A1)+6)

I first thought it was just a large date that started on a Thursday, dang if I can figure this one out. Anyway it don't add 7 if A1
is a Friday, it adds zero.
Here is a slight modification on my last post.

=7*(MOD(A2,7)5)-MOD(A2,7)+A2+6


Regards Robert

"JMay" wrote in message news:yovic.18727$VQ3.15219@lakeread06...
Dana,
Just curious but how did you derive the number 138612?
I see that it is the Key to this approach!!
TIA,


"Dana DeLouis" wrote in message
...
Just another option:

=A1+MOD(138612,WEEKDAY(A1)+6)

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"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

Hi. I think the Op wanted to add 0 due to the following...

=IF(WEEKDAY(NOW())=6,TODAY()+0,"")

If you are interested in adding 7 to a Friday along this idea, here is one
option:

=A1+MOD(6405928, 4*WEEKDAY(A1) + 13)

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Robert McCurdy" wrote in message
...
=A1+MOD(138612,WEEKDAY(A1)+6)

I first thought it was just a large date that started on a Thursday, dang
if I can figure this one out. Anyway it don't add 7 if A1
is a Friday, it adds zero.
Here is a slight modification on my last post.

=7*(MOD(A2,7)5)-MOD(A2,7)+A2+6

<snip