date question

  • Thread starter Thread starter Flub
  • Start date Start date
F

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
 
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
 
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)
 
Bob,
Like it but OP said "following" monday.

Bob Phillips said:
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
Click to expand...
 
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.
 
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))
Click to expand...

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

Regards,

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

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

Glenn
 
Back
Top