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
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
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.
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'
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).
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
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
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".
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
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.
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.
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.
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
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.
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'.
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()
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.
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.
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".
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.
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()
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.
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'.
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.
