# Time Functions

Time functions used by various Microsoft products

TAGS: Microsoft, Programming, Software, TECH, Time

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