How to get Monday's date when you enter any other date for the week
Category: [Excel] Demo Available
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.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to delete row if Date/Time between 05:00 - 20:00 |
| How to create a macro button to put date in selected cell within specific column |
| How to jump to the cell with current date when open an Excel file |
| How to format InputBox as date |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.