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
Advertisements

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