<calendar name>.Value = <calendar name>.Value <+/-> <number of days you want to move forward or back>
‘examples myCalendar.Value = myCalendar.Value + 1 'move forward one day monthview.Value = monthView.Value -5 'move backward 5 days
Tag: date
What is the syntax to get the date selected for a calendar?
- Add a calendar to your userform (for instructions, click here)
- As shown in the screenshot below, select your calendar control (default name is always MonthView1)
- Select DateClick, as shown circled in red below
- A procedure will be auto-generated, containing a parameter called DateClicked
- User’s selected date is automatically contained in variable DateClicked
What is the syntax to add a calendar to a userform?
VBA IDE > View > Toolbox > Right-click on Toolbox window > Additional Controls > check Microsoft MonthView Control 6.0 (SP6) > In Toolbox, select MonthView control > drag calendar onto your userform
What is the syntax to format a date?
FormatDateTime (date, format) ‘date – mandatory parameter ‘format – specifies which format to use; optional parameter
Format values
- 0 = vbGeneralDate – Default.
- 1 = vbLongDate
- 2 = vbShortDate
- 3 = vbLongTime
- 4 = vbShortTime
‘examples MsgBox (FormatDateTime("2064-08-15 20:25")) ‘returns 8/15/2064 8:25:00 PM MsgBox (FormatDateTime(#5/21/2075#, 1)) ‘returns Tuesday, May 21, 2015 MsgBox (FormatDateTime(#5/21/2075#, 2)) ‘returns 5/21/2075 MsgBox (FormatDateTime("12:34:17", 3)) ‘returns 12:34:17 PM MsgBox (FormatDateTime("2050-08-21 12:34:17", 4)) ‘returns 12:34
What is the syntax to return a date, given a year, month, day?
DateSerial (year, month, day) ‘all three parameters are mandatory 'year - range 100-9999 'month - range 1-12 'day - range 1-31
‘examples MsgBox (DateSerial(2050, 4, 23)) ‘returns 4/23/2050 MsgBox (DateSerial(22, 9, 17)) ‘returns 9/17/2022
What is the syntax to return part of an inputted date?
DatePart (interval, date1, firstdayofwk, firstdayofyr) ‘interval – can be one of the following listed in bullet points; mandatory ‘date1 – mandatory parameter ‘firstdayofweek – can be values 1-7 for Sunday-Saturday, or 0 for system’s version; optional parameter ‘firstdayofyear – really useless optional parameter, just ignore it
Interval values
- d – day of the year.
- m – month of the year
- yyyy -Year
- w -Weekday
- ww -week
- q -quarter
- h -Hour
- n -Minute
- s –Second
‘examples MsgBox (DatePart("yyyy", #3/22/2030#)) '2030 MsgBox (DatePart("d", #8/17/2060#)) '17 MsgBox (DatePart("q", "2057-01-15")) '1 MsgBox (DatePart("ww", "2031-12-27")) '52
What is the syntax to get the date after adding some time?
DateAdd (interval, number, date) 'all three are mandatory parameters ‘interval – can be one of the following listed in bullet points ‘number – can be any +/- integer 'date – can be variant or literal, represents starting date
Interval values
- d – day of the year.
- m – month of the year
- yyyy -Year
- w -Weekday
- ww -week
- q -quarter
- h -Hour
- n -Minute
- s –Second
‘examples MsgBox (DateAdd("yyyy", 1, #1/1/2040#)) '1/1/2041 MsgBox (DateAdd("q", 1, #1/17/2040#)) '4/17/2040 MsgBox (DateAdd("d", -10, #1/17/2040#)) '1/7/2040 MsgBox (DateAdd("ww", 1, date1)) '1/6/1900 MsgBox (DateAdd("h", -1, "01-Jan-2040 12:00:00")) '1/1/2040 11:00:00 AM MsgBox (DateAdd("n", 1, "01-Jan-2040 12:00:00")) '1/1/2040 12:01:00 PM MsgBox (DateAdd("s", 1, "01-Jan-2040 12:00:00")) '1/1/2040 12:00:01 PM
What is the syntax to convert an input to date?
cDate(<date>)
‘examples Dim example1, example2 As Variant example1 = CDate("January 1 2040") '01/01/2040 example2 = CDate("1 Jan 2040") '01/01/2040
What is the syntax to return the current date?
Date()
To hold this value in a variable, declare the type as Variant.
What is the syntax to get the current time?
Now() ‘returns current time and date Time() ‘returns just the time
What is the syntax to return the weekday name from date?
WeekDayName(weekday, abbreviate, firstdayofweek) ‘weekday can be values 1-7; mandatory parameter ‘abbreviate can be true or false; optional parameter with false default ‘firstdayofweek can be values 1-7 for Sunday to Saturday; optional parameter
‘examples MsgBox (WeekdayName(3)) 'returns Tuesday MsgBox (WeekdayName(1, True)) 'returns Sun MsgBox (WeekdayName(1, False)) 'returns Sunday MsgBox (WeekdayName(7, False, 6)) 'returns Thursday
What is the syntax to return the weekday from date?
WeekDay(date, firstdayofweek) ‘firstdayofweek can be values 1-7 for Sunday to Saturday; optional parameter
‘examples MsgBox (Weekday("2043-01-24")) 'returns 6 MsgBox (Weekday("2043-01-24", 2)) 'returns 7
What is the syntax to return the month’s name from date?
MonthName(month, toabbreviate) ‘month can be values 1-12; mandatory parameter ‘toabbreviate can be true or false; optional parameter that defaults to false
‘examples MsgBox (MonthName(1, True)) 'returns Jan MsgBox (MonthName(1)) 'returns January MsgBox (MonthName(12, False)) 'returns December
What is the syntax to return the month of a date?
Month(date) ‘returns a value from 1 to 12
‘examples MsgBox (Month("2086/5/25")) 'returns 5 MsgBox (Month("2086-5-25")) 'returns 5 MsgBox (Month(#5/25/2086#)) 'returns 5
What is the syntax to return the day of a date?
Day(date) ‘returns a value from 1 to 31
‘examples MsgBox (Day("2056/1/22")) 'returns 22 MsgBox (Day("2056-1-22")) 'returns 22 MsgBox (Day(#1/22/2056#)) 'returns 22
What is the syntax to format a date?
FormatDateTime(date, format) ‘date is a mandatory parameter ‘format is values 0-4 each representing a certain format; optional parameter
Possible values for format parameter:
- 0 = vbGeneralDate – Default
- 1 = vbLongDate – Returns date
- 2 = vbShortDate – Returns date
- 3 = vbLongTime – Returns time
- 4 = vbShortTime – Returns time
‘examples MsgBox (FormatDateTime("2064-08-15 20:25")) ‘returns 8/15/2064 8:25:00PM MsgBox (FormatDateTime(#5/21/2075#, 1)) ‘Tuesday, May 21, 2075 MsgBox (FormatDateTime(#5/21/2075#, 2)) ‘5/21/2075 MsgBox (FormatDateTime("12:34:17", 3)) ‘12:34:17 PM MsgBox (FormatDateTime("2050-08-21 12:34:17", 4)) ‘returns 12:34
MYSYNTAX: VBA 