How to Get a specific date when you enter any other dates for the week
Requirement:
To have Monday's date for the week whenever the date for any day of the week is entered: 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:
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:
| How to count weekdays in a month excluding holidays |
| How to get a specific date when you enter any other dates for the week |
| How to use calendar form control - only allow weekdays to be selected |
| How to get Monday's date when you enter any other date for the week |
| How to create VBA code for a workbook to work on week days only and specific range of time |
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
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.
Gallery
