Finding The Next Monday From a Date

[Charles]

Hi You All helped me with the number of days in a month
and I Thank you for it, I need help again with how to find
the next Monday from a date chosen by the user if he picks
a day other than Monday. Is this possible to do in VBA
code if so can someone point me in the right direction.
TIA
Charles

 

[Norman Harker]

Hi Charles!

Try:
=A1-WEEKDAY(A1-1)+8

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Frank Kabel]

Hi Charles
try the following (if A1 stores your base date)
=A1+(WEEKDAY(A1)>2)*7-WEEKDAY(A1)+2

 

[Norman Harker]

Hi Charles!

My approach finds the next Monday. However, if the user puts in (say)
Monday 8-Mar-2004 it will return Monday 15-Mar-2004. You may not want
this! If you want either the same day, if a Monday or the next Monday
then use:

=IF(WEEKDAY(A1)=2,A1,A1-WEEKDAY(A1-1)+8)


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Frank Kabel]

Hi Charles!

My approach finds the next Monday. However, if the user puts in (say)
Monday 8-Mar-2004 it will return Monday 15-Mar-2004. You may not want
this! If you want either the same day, if a Monday or the next Monday
then use:

=IF(WEEKDAY(A1)=2,A1,A1-WEEKDAY(A1-1)+8)

or without IF function
=A1+(WEEKDAY(A1)>2)*7-WEEKDAY(A1)+2

 

[Norman Harker]

Hi Frank!

Sure! Although I suppose you could say that you're still using an
implicit IF function.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Guest]

Sub mon(
a = Range("a2").Formul
For c = 1 To
y = a +
If Format(y, "ddd") = "Mon" Then Range("a3").Value = Format(a + c, "mm/dd/yyyy"
Next
End Sub

 

[Frank Kabel]

Hi Frank!

Sure! Although I suppose you could say that you're still using an
implicit IF function.

You're right
Was just in my picky mood

Greetings to Australia
Frank

 

[Norman Harker]

Hi Frank!

Nothing wrong with being picky!

And another implicit approach:

=A1-WEEKDAY(A1-1)+8-(WEEKDAY(A1)=2)*7

Greeting to Germany from a still hot Australia.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.