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

From: Bob Phillips (bob.phillips_at_notheretiscali.co.uk)
Date: 04/24/04 

Date: Sat, 24 Apr 2004 13:37:30 +0100

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" <twogilvy@msn.com> wrote in message
news:uLQlHefKEHA.3332@TK2MSFTNGP10.phx.gbl...
> =Today()+Choose(weekday(today()),5,4,3,2,1,0,6)
>
> -- 
> Regards,
> Tom Ogilvy
>
> "Austin M. Horst" <anonymous@discussions.microsoft.com> wrote in message
> news:1F7A5EF0-2FCA-456D-9BFE-691974BC1127@microsoft.com...
> > 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
>
>

Relevant Pages