Finding The Next Monday From a Date

  • Thread starter Thread starter Charles
  • Start date Start date
C

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
 
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.
 
Hi Charles
try the following (if A1 stores your base date)
=A1+(WEEKDAY(A1)>2)*7-WEEKDAY(A1)+2
 
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.
 
Norman said:
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)
Click to expand...

or without IF function :-)
=A1+(WEEKDAY(A1)>2)*7-WEEKDAY(A1)+2
 
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.
 
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
 
Norman said:
Hi Frank!

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

You're right
Was just in my picky mood :-)

Greetings to Australia
Frank
 
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.
 
Back
Top