Prev: Yearly sum
Next: delete columns in spreadsheet after data entered so it wont scroll
From: Ted on 19 Mar 2010 15:00
This worked great. Thanks everyone for your help.

"Luke M" <lukemoraga(a)nospam.com> wrote in message
news:ebsqVIRxKHA.4492(a)TK2MSFTNGP05.phx.gbl...
> If today is Monday, do you want today's date or next week?
> former:
> =TODAY()+CHOOSE(WEEKDAY(TODAY()),1,0,6,5,4,3,2)
> latter:
> =TODAY()+CHOOSE(WEEKDAY(TODAY()),1,7,6,5,4,3,2)
> --
> Best Regards,
>
> Luke M
> "Ted" <ted.gallagher(a)gmail.com> wrote in message
> news:OA4Ch%23QxKHA.5940(a)TK2MSFTNGP02.phx.gbl...
>> Hello:
>>
>> I am seeking a formula that would return the date of the closest Monday
>> to today's date. The monday would have to be the first one in the
>> future, not the past. So today, Tuesday March 16, 2010, the formula
>> would return Monday, March 22, 2010
>>
>> Thanks in advance :)
>
>
From: Ron Rosenfeld on 19 Mar 2010 15:41
On Tue, 16 Mar 2010 08:50:26 -0500, "Ted" <ted.gallagher(a)gmail.com> wrote:

>Hello:
>
>I am seeking a formula that would return the date of the closest Monday to
>today's date. The monday would have to be the first one in the future, not
>the past. So today, Tuesday March 16, 2010, the formula would return
>Monday, March 22, 2010
>
>Thanks in advance :)

To always return the NEXT Monday (even if today is a Monday)

=A1+8-WEEKDAY(A1+6)

or, more generally:

=A1+8-WEEKDAY(A1+8-DOW)

DOW: 1=Sun, 2=Mon, etc.

To return the NEXT Monday EXCEPT if today is a Monday:

=A1+7-WEEKDAY(A1+5)

or

=A1+7-WEEKDAY(A1+7-DOW)
--ron
First  |  Prev  | 
Pages: 1 2
Prev: Yearly sum
Next: delete columns in spreadsheet after data entered so it wont scroll