How to Get Monday's Date when you enter any other date for the week

How to get Monday's date when you enter any other date for the week

Requirement:

The user wants to have Monday's date for the week whenever i enter the date for any day of the week: From Sunday to Saturday

Example: if the user enters 08/18/19(which is Sunday), it should make it 08/19/19 (that is Monday), if the user enters 08/19/19 (Which is Monday) stays 08/19/19.. and any date from 08/20/19 to 8/24/19 should make it 08/19/19 (Monday's date).

Solution:

Code:

=DATE(YEAR(A1),MONTH(A1),DAY(A1)+2-WEEKDAY(A1))

The above works however, the user also wants to know how to use this in VBA:

Below is a macro proposal:

Code:

Sub Monday()
Dim mday As String
mday = "8/21/19"
MsgBox Format(Evaluate("=DATE(YEAR(" & CLng(CDate(mday)) & "),MONTH(" & CLng(CDate(mday)) & "),DAY(" & CLng(CDate(mday)) & ")+2-WEEKDAY(" & CLng(CDate(mday)) & "))"), "mm/dd/yyyy")
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by Carim.