What is the syntax to auto-move days on a calendar?

<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
Advertisements

What is the syntax to get the date selected for a calendar?

  1. Add a calendar to your userform (for instructions, click here)
  2. As shown in the screenshot below, select your calendar control (default name is always MonthView1)
  3. Select DateClick, as shown circled in red below
  4. A procedure will be auto-generated, containing a parameter called DateClicked
  5. User’s selected date is automatically contained in variable DateClicked

unnamed.png

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 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