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

A

Austin M. Horst

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
 
C

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
 
B

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

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
[email protected]

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

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
 
J

JMay

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

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
 
R

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
 
D

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


Robert McCurdy said:
I first thought it was just a large date that started on a Thursday, dang
Click to expand...
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
Click to expand...

<snip>
 
Top