Formula to return Friday's date: m/d/yyyy

[Guest]

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

 

[Tom Ogilvy]

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

 

[Chip Pearson]

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



message
news:[email protected]...

 

[Chip Pearson]

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

 

[Bob Phillips]

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)

 

[Norman Harker]

Hi Chip!

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

Missing parentheses.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)

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/

 

[John Baker]

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

 

[Dana DeLouis]

Just another option:

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

 

[Austin M. Horst]

Thank you all.
Each one works great!


Austin M. Horst

 

[JMay]

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

 

[Robert McCurdy]

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

 

[Robert McCurdy]

=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

 

[Dana DeLouis]

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

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
= = = = = = = = = = = = = = = = =

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>