Intro

This page goes over time and date functions for the following Microsoft products: Access, Excel, SQL Server, VBScript, Visual Basic and C#.

Dates and times are numerically stored as a number where the integer portion represents a day after some some base date while the fractional portion represents a fraction of a day, i.e., a time between midnight and 11:59:59 PM. Different systems have different base dates. EGs:

  • Access and SQL Server start out at 1900.
  • Excel starts out at either 1900 or 1904.
  • Google Spreadsheets start out at 1900, i.e. 0 = 1899-12-30 00:00:00

If possible, the following values will be used in the examples:

  • Friday, February 28, 1969 as the date
  • 4:35:17 PM as the time
  • 1,200.071 as the numeric value

Set Date or Time

' In VB changes current system date:
Date = #February 28, 1969#
Date = 25262
' In VB changes current system time:
Time = #4:35:17 PM#
Time = 0.691169

Get System Date or Time

Get the current system date.

MyDate = Date()    ' In Access
MyDate = Date      ' In VB

Get the current system time.

MyTime = Time()    ' In Access
MyTime = Time()    ' In Excel returns serial number
MyTime = Time      ' In VB

Get the current system date and time.

MyNow = Now()         ' In Access
MyNow = Now()         ' In Excel returns serial number
@MyNow = GETDATE()    -- In SQL Server
MyNow = Now           ' In VB

Return a Date or Time

MyDate = DateSerial(1969, 2, 26 + 2)
' Returns an integer equivalent to date. 'Not in Excel
MyDate = DateValue(#February 28, 1969#)
' Returns an integer equivalent to date. 'In both of the above cases CInt(MyDate) yields 25262.
MyTime = TimeSerial(16, 35, 10 + 7)
' Returns a fraction equivalent to time. 'Not in Excel
MyTime = TimeValue(#4:35:17 PM#)
' Returns a fraction equivalent to time. 'In both of the above cases CSng(MyTime) yields 0.691169.

Time a Process

MyTime = Timer
' Returns the number of seconds elapsed since midnight.

Perform Date Calculations

DateAdd(IntervalType, QuantityOfIntervals, Date)
' Returns (Date1 + n*Interval).
DateAdd("h", 1, #3:35:17 PM#)
' Returns #4:35:17 PM#.
DateDiff(IntervalType, Date1, Date2[, FirstDayOfWeek>[, FirstWeekOfYear]])
' Returns Date2 - Date1.
DateDiff("h", #3:35:17 PM#, #4:35:17 PM#)
' Returns 1.
DatePart(IntervalType, Date[, FirstDayOfWeek[, FirstWeekOfYear]])
' Returns specified part of Date1.
DatePart("h", #4:35:17 PM#)
' Returns 16.
DateName(IntervalType, Date)
' Returns text representing part of Date1. This is a SQL Server function.
DateName(m, '02/28/1969')
' Returns February.

The FirstDayOfWeek defaults to 1 = Sunday.
The FirstWeekOfYear defaults to 1 = start with week in which January 1 occurs.

In Excel, the EDate(StartDate,Months) and EOMonth(StartDate,Months) functions are convenient functions that add or subtract a month while taking into account the different lengths of months. Those functions are only available if you add them on via Select Tools > Add-Ins > Analysis ToolPak.

IF(A1=EOMONTH(A1,0),EOMONTH(A1,1),EDATE(A1,1))
' Returns next month

If A1 = 2004-03-31, then it returns 2004-02-29.

The following are the various IntervalTypes:

IntervalType Abbreviation Values
Year yy, yyyy 1753-9999 (depends on system settings) 
Quarter qq, q 1-4
Month mm, m 1-12
Day of Year dy, y 1-366
Day dd, d 1-31
Weekday dw, w 1-7 (Sun. - Sat.)
Week wk, ww 1-53
Hour hh, h 0-23
Minute mi, n 0-59
Second ss, s 0-59
Millisecond ms 0-999

T-SQL uses either the fully spelled IntervalType or the double-letter abbreviations.

Return Integer Values for Parts of a Date

MyDate = #2/28/69 4:35:17 PM#
MyValue = Year(MyDate)    ' Returns 1969.
MyValue = Month(MyDate)   ' Returns 2.
MyValue = Day(MyDate)     ' Returns 28.
MyValue = WeekDay(MyDate) ' Returns 6 since 2/28/69 was a Friday.
MyValue = Hour(MyTime)    ' Returns 16.
MyValue = Minute(MyTime)  ' Returns 35.
MyValue = Second(MyTime)  ' Returns 17.

Formatting Dates, Numbers, and Strings

An expression is formatted by either a valid named format or a user-defined format:

Format(<Expression>[, <Format>[, <FirstDayOfWeek>[, <FirstWeekOfYear>]]])

In VBScript the following is used in place of the above:

FormatDateTime(<Expression>[,<named format>])
' The named format can only be one of these:
' vbGeneralDate (default)
' vbLongDate
' vbShortDate
' vbLongTime
' vbShortTime

There are various system-defined formats:

  • Date and Times. The output will vary depending on the system's regional settings.
    • Long Date or D (in C#) usu. Friday, February 28, 1969 or February 28, 1969.
    • Medium Date, usu. 28-Feb-69.
    • Short Date or ddddd or d (in C#), usu. 2/12/69 in the US, 12/2/69 in the UK, and 69/2/12 for ISO.
    • Long Time or ttttt or t (in C#), usu. 4:35:17 PM.
    • Medium Time, usu. 4:35 PM.
    • Short Time or t (in C#), usu. 16:35.
    • Full Date & Time or f (in C#), usu. adapts to the data as Long Date, Short Time, or both.
    • Full Date & Time Longer or F (in C#),, usu. adapts to the data as Long Date, Long Time, or both.
    • General Date & Time or g (in C#), usu. adapts to the data as Short Date, Short Time, or both.
    • General Date & Time Longer or G (in C#), adapts to the data as Short Date, Long Time, or both.
    • RFC 1123/822 or r (in C#), ddd, dd MMM yyyy HH:mm:ss ZZZ. EG: Fri, 28 Feb 1969 16:35:17 GMT.
    • Sortable Date or s (in C#), ISO 8601 with no timezone: yyyy-MM-ddTHH:mm:ss. EG: 1969-02-28T16:35:17.
    • Universal Sortable Date ISO or u (in C#), ISO 8601 with timezone: yyyy-MM-ddTHH:mm:ssZ. EG: 1969-02-28T16:35:17Z.
    • Universal Sortable Date RFC variant or U (in C#), just like Full Date & Time Long.
    • Month Day or M (in C#). EG: February 02.
    • Year Month or Y (in C#). EG: February, 1969
  • Numbers
    • General Number or g (in C#), plain number. EG: 1200.071.
    • Whole Number or Decimal or d (in C#), integers. EG: 1200.
    • Fixed or f (in C#), at least one digit on the left and a standard number of digits the right. EG: 1200.07.
    • Currency or c (in C#). EG: $1,200.07.
    • Scientific or e (in C#). EG: usu. 1.200071e+3.
    • Number with separators or n (in C#). EG: 1,200.071.
    • Percent, multiplies the value by 100 and places a % (percent sign) at the end. EG: 120007.1%.
    • Yes/No, nonzero is "Yes", zero is "No".
    • True/False, nonzero is "True", zero is "False".
    • On/Off, nonzero is "On", zero is "Off".

The following symbols can be used to make custom formats. Note the case sensitivity.

  • Dates and Times
    • yy (in C#), last 2 digits of the year (00 to 99).
    • yyyy (in C#), full year (0100 to 9999).
    • M, month in one or two digits (1 to 12).
    • MM (in C#), month in two digits (01 to 12).
    • MMM (in C#), month in three letters (Jan to Dec).
    • MMMM (in C#), month spelled out (January to December).
    • ww, number of the week of the year (1 to 53).
    • d, day in one or two digits (1 to 31).
    • dd (in C#), day in two digits (01 to 31).
    • ddd (in C#), day in three letters (Sun to Sat).
    • dddd (in C#), day spelled out (Sunday to Saturday).
    • gg (in C#), era "A.D.". This is a silly one. What about BC, BCE, and CE?
    • q, Date displayed as quarter of the year (1 to 4).
    • w, number of the day of the week (1 to 7).
    • y, number of the day of the year (1 to 366).
    • h, hour in one or two digits (0 to 12).
    • hh (in C#), hour in two digits (00 to 12).
    • H, military hour in one or two digits (0 to 23).
    • HH (in C#), military hour in two digits (00 to 23).
    • m or n, minute in one or two digits (0 to 59).
    • mm (in C#) or nn, minute in two digits (00 to 59).
    • s, second in one or two digits (0 to 59).
    • ss (in C#), second in two digits (00 to 59).
    • f, ff, or fff (in C#), second fractions in one to three digits.
    • AM/PM, show if time is either "AM" or "PM".
    • am/pm, show if time is either "am" or "pm".
    • A/P, show if time is either "A" or "P".
    • a/p, show if time is either "a" or "p".
    • AMPM, show if time is either morning or evening symbol as system defined.
    • t, , show if time is either morning or evening symbol as system defined, using one character. EG: "a" or "p".
    • tt (in C#), show if time is either morning or evening symbol as system defined, using one character. EG: "AM" or "PM".
    • z, timezone offset in military letters, EG: "S" or "Z".
    • zz (in C#), timezone offset in 2 digits. EG: "-06" or "00".
    • zzz (in C#), timezone offset in 4 digits. EG: "-06:00" or "00:00".
    • ZZZ, timezone in three letter acronyms. EG: "CST" or "GMT".
  • Numbers
    • 0 (in C#), digit place holder, pads with zeros. EG: 00000 makes 01200.
    • # (in C#), digit place holder, does not pad with zeros. EG: #.## makes1200.07.
    • . (in C#), decimal point place holder (as system defined, usu. ".").
    • , (in C#), thousands place holder (as system defined, usu. ","). EG: #,# makes 1,200.
    • % (in C#), multiplies the value by 100 and places a % (percent sign) at the end. EG: % makes 120007.1%.
    • e (in C#), converts to scientific notation. EG: 0.0e+0 makes 1.2e+3.
    • / (in C#) date separator (as system defined, usu. "/").
    • : (in C#), time separator (as system defined, usu. ":").
    • ( (in C#), ) (in C#), (space) (in C#), - (in C#), + (in C#), and $ (in C#) are the only literal characters allowed. All other literal characters must be escaped with a backslash first. EG: \x.
  • Text
    • @, character holder, pads with spaces. EG: " Hi".
    • &, character holder, does not pad with spaces. EG: "Hi".
    • !, fill place holder left to right instead of the usual right to left.
    • >, displays following data in all caps. EG: "HI".
    • <, displays following data in lower case. EG: "hi."

EGs:

MyTime = #2/28/69 4:35:17 PM#
MyStr = Format(MyTime, "H:m:s")            ' Returns "16:35:17".
MyStr = Format(MyTime, "hh:mm:ss AMPM")    ' Returns "04:35:17 PM".
MyStr = Format(MyTime, "dddd, mmm d yyyy") ' Returns "Wednesday, Feb 12 1969".
' User-defined formats.
MyStr = Format(5459.4, "##,##0.00")        ' Returns "5,459.40".
MyStr = Format(334.9, "###0.00")           ' Returns "334.90".
MyStr = Format(5, "0.00%")                 ' Returns "500.00%".
MyStr = Format("HELLO", "<")               ' Returns "hello".
MyStr = Format("This is it", ">")          ' Returns "THIS IS IT".
' If format is not supplied, a string is returned.
MyStr = Format(1200.071)                   ' Returns "1200.071".
// C# example.
xFormatted = String.Format("{$#,0.00; ($#,0.00);Zero}, x)
// The semicolon separates 3 formatting possibilities: positive, negative, and zero.
// If x=1200.071 , then the above returns $1,000.00
// If x=-1200.071, then the above returns ($1,000.00)
// If x=0 , then the above returns Zero


GeorgeHernandez.comSome rights reserved