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 return the year of a date?

Year(date)

‘examples
MsgBox (Year("2074/3/18")) 'returns 2074
MsgBox (Year("2074-3-18")) 'returns 2074
MsgBox (Year(#3/18/2074#)) 'returns 2074