date question

[Flub]

Hi All
I'm trying to find a formula that will enter in
a particular cell e.g.A1 the date for the following
Monday regardless of which day this week the
sheet is opened.
Thanks for your time and any help

Glenn

 

[Bob Phillips]

Glenn,

Two options

First, if today is a Monday and you want today's date, then
=TODAY()+((WEEKDAY(TODAY(),2)<>1)*(-WEEKDAY(TODAY(),2)+8))

Second, if today is a Monday, and you want following Monday
=TODAY()-WEEKDAY(TODAY(),2)+8

 

[Don Guillett]

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

 

[Bob Phillips]

I meant to add that one (especially after an exchange with Myrna recently),
but still forgot.

On my point as to whether you want the same Monday or following if today is
a Monday, this version handles the alternative

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

 

[Don Guillett]

Bob,
Like it but OP said "following" monday.

I meant to add that one (especially after an exchange with Myrna recently),
but still forgot.

On my point as to whether you want the same Monday or following if today is
a Monday, this version handles the alternative

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

--

HTH

Bob Phillips

 

[Bob Phillips]

Don,

Yeah I know, and I took it literally as well at first, but you know users,
they don't always say what they mean, or more likely don't understand the
exact interpretation that we put on such statements. I thought best to cover
both eventualities, and the OP can always choose.

 

[Daniel.M]

Hi,

Two options

First, if today is a Monday and you want today's date, then
=TODAY()+((WEEKDAY(TODAY(),2)<>1)*(-WEEKDAY(TODAY(),2)+8))

For current/next Monday:
=TODAY()-WEEKDAY(TODAY()-2)+7

Regards,

Daniel M.

 

[Bob Phillips]

Interesting approach. Not as intuitive as mine (IMO), but shorter and
better.

Bob

 

[Flub]

Thanks everybody and Daniel in particular
works like a charm.
Again thanks for your time and input
Regards

Glenn