LikeOffice.com

excel utility

Keep In Touch:
 contact us  facebook
 
Get HELP with your Excel Project:
 Excel Formula
 List of Excel Formula examples
 
Home >> Excel Functions >> Excel Date & Time Functions
If you spend time on your Excel Project and you feel it can be done some other way, you may contact our Excel Help Team to help you with your Excel Project or use our Excel Question page.

More practical features can be found in our Excel Utility.


Excel's Date Functions can be used to add such things as the current date, the current time, or the day of the week to a spreadsheet.

Download our FREE Excel Utility
Provide many features and shortcuts to your Excel use
 


Category Function Description
Date & Time Date Date function returns the serial number of a date....


Date & Time Date (VBA) Date function returns the current system date. Th...
Date & Time DateAdd (VBA) DateAdd function returns a date after which a cert...
Date & Time DateDif DateDif function returns the difference between tw...
Date & Time DateDiff (VBA) DateDiff function returns the difference between t...
Date & Time DatePart (VBA) DatePart function returns a specified part of a gi...
Date & Time DateSerial (VBA) DateSerial function returns a date given a year, m...
Date & Time DateValue DateValue function returns the serial number of a ...
Date & Time Day Day function returns the day of the month (a numbe...
Date & Time Days360 Days360 function returns the number of days betwee...
Date & Time Hour Hour function returns the hour of a time value (fr...
Date & Time Minute Minute function returns the minute of a time value...
Date & Time Month Month function returns the month (a number from 1 ...
Date & Time MonthName (VBA) MonthName function returns a string representing t...
Date & Time Now Now function returns the current system date and t...
Date & Time Second Second function returns the second of a time value...
Date & Time Time Time function returns the decimal number for a par...
Date & Time TimeSerial (VBA) TimeSerial function returns a time given an hour, ...
Date & Time TimeValue TimeValue function returns the serial number of a ...
Date & Time Today Today function returns the current system date. Th...
Date & Time Weekday Weekday function returns a number representing the...
Date & Time WeekdayName (VBA) WeekdayName function returns a string representing...
Date & Time Year Year function returns a four-digit year (a number ...

Date

In Excel, the Date function returns the serial number of a date. The syntax for the Date function is: Date( year, month, day ) year is a number that is between one and four digits. If the year is between 0 and 1899, the year value is added to 1900 to determine the year. If the year is between 1900 and 9999, the Date function uses the year value as the year. If the year is greater than 9999, the Date function will return the #NUM! error. month is a number representing the month value. If the month value is greater than 12, the Date function adds that number of months to the first month of the year specified in the year parameter. day is a number representing the day value. If the day value is greater than the number of days in the month specified, the Date function add that number of days to the first day in the month specified in the month parameter

Top

 

Date (VBA)

In Excel, the Date function returns the current system date. The syntax for the Date function is: Date() For example: Date() would return a value such as '22/11/2003' VBA Code The Date function can only be used in VBA code. For example: Dim LDate As String LDate = Date In this example, the variable called LDate would now contain the current system date.

Top

 

DateAdd (VBA)

In Excel, the DateAdd function returns a date after which a certain time/date interval has been added. The syntax for the DateAdd function is: DateAdd ( interval, number, date ) interval is the time/date interval that you wish to add. It can be one of the following values: Value Explanation yyyy Year q Quarter m Month y Day of the year d Day w Weekday ww Week h Hour n Minute s Second number is the number of intervals that you wish to add. date is the date to which the interval should be added. For example: DateAdd("yyyy", 3, "22/11/2003") would return '22/11/2006' DateAdd("q", 2, "22/11/2003") would return '22/05/2004' DateAdd("m", 5, "22/11/2003") would return '22/04/2004' DateAdd("n", 51, "22/11/2003 10:31:58 AM") would return '22/11/2003 11:22:58 AM' DateAdd("yyyy", -1, "22/11/2003") would return '22/11/2002' VBA Code The DateAdd function can only be used in VBA code. For example: Dim LDate As Date LDate = DateAdd("s", 53, "22/11/2003 10:31:58 AM") In this example, the variable called LDate would now contain the value of '22/11/2003 10:32:51 AM'

Top

 

DateDif

In Excel, the DateDif function returns the difference between two date values, based on the interval specified. The syntax for the DateDif function is: DateDif( start_date, end_date, interval ) start_date and end_date are the two dates to calculate the difference between. interval is the interval of time to use to calculate the difference between date1 and date2. Below is a list of valid interval values. Interval Explanation Y The number of complete years. M The number of complete months. D The number of days. MD The difference between the days (months and years are ignored). YM The difference between the months (days and years are ignored). YD The difference between the days (years and dates are ignored).

Top

 

DateDiff (VBA)

In Excel, the DateDiff function returns the difference between two date values, based on the interval specified. It can only be used in VBA code. The syntax for the DateDiff function is: DateDiff( interval, date1, date2, [firstdayofweek], [firstweekofyear] ) interval is the interval of time to use to calculate the difference between date1 and date2. Below is a list of valid interval values. Interval Explanation yyyy Year q Quarter m Month y Day of year d Day w Weekday ww Week h Hour n Minute s Second date1 and date2 are the two dates to calculate the difference between. firstdayofweek is optional. It is a constant that specifies the first day of the week. If this parameter is omitted, Excel assumes that Sunday is the first day of the week. firstweekofyear is optional. It is a constant that specifies the first week of the year. If this parameter is omitted, Excel assumes that the week containing Jan 1st is the first week of the year. For example: The DateDiff function can only be used in VBA code as follows: Function TestDates (pDate1 as Date, pDate2 as Date) as Long TestDates = DateDiff("d", pDate1, pDate2) End Function Based on the spreadsheet below, the function would return the following values: =TestDates(A2, A1) would return 1. =TestDates(A3, A2) would return 16. =TestDates(A4, A3) would return 14

Top

 

DatePart (VBA)

In Excel, the DatePart function returns a specified part of a given date. The syntax for the DatePart function is: DatePart( interval, date, [firstdayofweek], [firstweekofyear] ) interval is the interval of time that you wish to return. This parameter can be any one of the following valid interval values: Interval Explanation yyyy Year q Quarter m Month y Day of year d Day w Weekday ww Week h Hour n Minute s Second date is the date value that you wish to evaluate. firstdayofweek is optional. It is a constant that specifies the first day of the week. If this parameter is omitted, Excel assumes that Sunday is the first day of the week. This parameter can be one of the following values: Constant Value Explanation vbUseSystem 0 Use the NLS API setting vbSunday 1 Sunday (default) vbMonday 2 Monday vbTuesday 3 Tuesday vbWednesday 4 Wednesday vbThursday 5 Thursday vbFriday 6 Friday vbSaturday 7 Saturday firstweekofyear is optional. It is a constant that specifies the first week of the year. If this parameter is omitted, Excel assumes that the week containing Jan 1st is the first week of the year. This parameter can be one of the following values: Constant Value Explanation vbUseSystem 0 Use the NSL API setting vbFirstJan1 1 Use the first week that includes Jan 1st (default) vbFirstFourDays 2 Use the first week in the year that has at least 4 days vbFirstFullWeek 3 Use the first full week of the year For example: DatePart("yyyy", "15/10/1998") would return 1998 DatePart("m", "15/10/2003") would return 10 DatePart("d", "15/10/2003") would return 15 VBA Code The DatePart function can only be used in VBA code. For example: Dim LValue As Integer LValue = DatePart("d", "15/10/2003") In this example, the variable called LValue would now contain the value of 15

Top

 

DateSerial (VBA)

In Excel, the DateSerial function returns a date given a year, month, and day value. The syntax for the DateSerial function is: DateSerial( year, month, day ) year is a numeric value between 100 and 9999 that represents the year value of the date. month is a numeric value that represents the month value of the date. day is a numeric value that represents the day value of the date. For example: DateSerial(2004, 6, 30) would return "6/30/2004" DateSerial(2004-1, 6, 30) would return "6/30/2004" DateSerial(2004, 6-2, 14) would return "4/14/2004" VBA Code The DateSerial function can only be used in VBA code. For example: Dim LDate As Date LDate = DateSerial(2004, 5, 31) In this example, the variable called LDate would now contain the value of "5/31/2004".

Top

 

DateValue

In Excel, the DateValue function returns the serial number of a date. The syntax for the DateValue function is: DateValue( date ) date is a string representation of a date

Top

 

Day

In Excel, the Day function returns the day of the month (a number from 1 to 31) given a date value. The syntax for the Day function is: Day( date ) date_value is a valid date.

Top

 

Days360

In Excel, the Days360 function returns the number of days between two dates based on a 360-day year. The syntax for the Days360 function is: Days360( start_date, end_date, method ) start_date and end_date are the two dates to calculate the difference between. method is a boolean value - either TRUE or FALSE. If TRUE is entered, the Days360 function will use the US method. If FALSE is entered, the Days360 function will use the European method.

Top

 

Hour

In Excel, the Hour function returns the hour of a time value (from 0 to 23). The syntax for the Hour function is: Hour( serial_number ) serial_number is the time value to extract the hour from. It may be expressed as a string value, a decimal number, or the result of a formula.

Top

 

Minute

In Excel, the Minute function returns the minute of a time value (from 0 to 59). The syntax for the Minute function is: Minute( serial_number ) serial_number is the time value to extract the minute from. It may be expressed as a string value, a decimal number, or the result of a formula

Top

 

Month

In Excel, the Month function returns the month (a number from 1 to 12) given a date value. The syntax for the Month function is: Month( date_value ) date_value is a valid date.

Top

 

MonthName (VBA)

In Excel, the MonthName function returns a string representing the month given a number from 1 to 12. The syntax for the MonthName function is: MonthName( number, [ abbreviate ] ) number is a value from 1 to 12, representing the month. abbreviate is optional. This parameter accepts a boolean value, either TRUE or FALSE. If this parameter is set to TRUE, it means that the month name is abbreviated. If this parameter is set to FALSE, the month name is not abbreviated. For example: MonthName(3) would return 'March' MonthName(3, TRUE) would return 'Mar' MonthName(7, FALSE) would return 'July' VBA Code The MonthName function can only be used in VBA code. For example: Dim LValue As Integer LValue = MonthName(3, TRUE) In this example, the variable called LValue would now contain the value of 'Mar'.

Top

 

Now

In Excel, the Now function returns the current system date and time. This function will refresh the date/time value whenever the worksheet recalculates. The syntax for the Now function is: Now()

Top

 

Second

In Excel, the Second function returns the second of a time value (from 0 to 59). The syntax for the Second function is: Second( serial_number ) serial_number is the time value to extract the second from. It may be expressed as a string value, a decimal number, or the result of a formula.

Top

 

Time

In Excel, the Time function returns the decimal number for a particular time. The syntax for the Time function is: Time( hour, minute, second ) hour is a number from 0 to 23, representing the hour. minute is a number from 0 to 59, representing the minute. second is a number from 0 to 59, representing the second.

Top

 

TimeSerial (VBA)

In Excel, the TimeSerial function returns a time given an hour, minute, and second value. The syntax for the TimeSerial function is: TimeSerial( hour, minute, second ) hour is a numeric value between 0 and 23 that represents the hour value of the time. minute is a numeric value that represents the minute value of the time. second is a numeric value that represents the second value of the time. For example: TimeSerial(14, 6, 30) would return 2:06:30 PM TimeSerial(20 - 8, 6, 30) would return 12:06:30 PM TimeSerial(8, 6-2, 14) would return 8:04:14 AM TimeSerial(7, -15, 50) would return 6:45:50 AM VBA Code The TimeSerial function can only be used in VBA code. For example: Dim LTime As Date LTime = TimeSerial (23, 5, 31) In this example, the variable called LTime would now contain the value of "11:05:31 PM".

Top

 

TimeValue

In Excel, the TimeValue function returns the serial number of a time. The syntax for the TimeValue function is: TimeValue( time_value ) time_value is a string representation of a time.

Top

 

Today

In Excel, the Today function returns the current system date. This function will refresh the date whenever the worksheet recalculates. The syntax for the Today function is: Today()

Top

 

Weekday

In Excel, the Weekday function returns a number representing the day of the week, given a date value. The syntax for the Weekday function is: Weekday( serial_number, return_value ) serial_number is a date expressed as a serial number or a date in quotation marks. return_value is optional. It is the option used to display the result. It can be any of the following values: Value Explanation 1 Returns a number from 1 (Sunday) to 7 (Saturday). This is the default if parameter is omitted. 2 Returns a number from 1 (Monday) to 7 (Sunday). 3 Returns a number from 0 (Monday) to 6 (Sunday). If this parameter is omitted, the Weekday function assumes that the return_value is set to 1.

Top

 

WeekdayName (VBA)

In Excel, the WeekdayName function returns a string representing the day of the week given a number from 1 to 7. The syntax for the WeekdayName function is: WeekdayName( number, [ abbreviate], [ firstdayofweek ] ) number is a value from 1 to 7, representing a day of the week. abbreviate is optional. This parameter accepts a boolean value, either TRUE or FALSE. If this parameter is set to TRUE, it means that the weekday name is abbreviated. If this parameter is set to FALSE, the weekday name is not abbreviated. firstdayofweek is optional. It determines what day is to be the first day of the week. It can be any of the following values: Constant Value Explanation vbUseSystem 0 Use the NLS API settings vbSunday 1 Sunday (default used) vbMonday 2 Monday vbTuesday 3 Tuesday vbWednesday 4 Wednesday vbThursday 5 Thursday vbFriday 6 Friday vbSaturday 7 Saturday If this parameter is omitted, the Weekday function assumes that the first day of the week is Sunday. Please note that if you use the Weekday function in a query, you'll have to use the numeric value (ie: 0 to 7) for the firstdayofweek parameter. You can only use the constant equivalent (ie: vbSunday to vbSaturday) in VBA code. For example: WeekdayName(3) would return 'Tuesday' WeekdayName(3, TRUE) would return 'Tue' WeekdayName(3, TRUE, vbMonday) would return 'Wed' WeekdayName(3, TRUE, 2) would return 'Wed' VBA Code The WeekdayName function can only be used in VBA code. For example: Dim LValue As Integer LValue = WeekdayName(3, TRUE, vbMonday) In this example, the variable called LValue would now contain the value of 'Wed'.

Top

 

Year

In Excel, the Year function returns a four-digit year (a number from 1900 to 9999) given a date value. The syntax for the Year function is: Year( date_value ) date_value is a valid date.

Top

 

                  

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
©2003-2013 LikeOffice - Privacy Policy - Contact us