Excel Date Functions
The DATE Function gives the Excel date-time code for a specific date. It takes the inputs of Year, Month, Day. Inputting 2012,2,29 for Year/Month/Day gives a result of 40968.
DATE VALUE takes in a date in a mostly numerical format for example 1/1/2012 entered for the date gives a Microsoft date time code of 40909.
The DAY function takes a Excel date code and gives a number of 1 to 31 to show the date of the month.
The DAYS360 tells us the number of days between two dates on a 360 day calendar with twelve months of thirty days. It takes as inputs the starting date, the ending date, and a TRUE / FALSE for the method to use in calculating the dates.
EDATE returns an excel date code for the date that is the number of months before or after the starting date. The inputs are Starting Date and Months. Use negative numbers for before the starting date and positive numbers after the starting date. The starting date number needs to be in Excel’s date-time format. It outputs in the date-time format.
EOMONTH is like EDATE, but it will return the date-time code of the last day of the month before or after the start date.
NETWORKDAYS will tell us how many working days are between two dates. Use the date-time format to tell Excel what the start and end dates are. You can also enter dates in the date-time format which are holidays. NETWORKDAYS does not count weekends, or the holidays.
YEAR takes a date-time formatted date and returns its year.
YEARFRAC returns the fractional of a year between two dates. Enter your start and end dates in date-time format. Then tell Excel which basis to use.
The basis you can use are (From Excel 2007 Online Help):
|Basis||Day count basis|
|0 or omitted||US (NASD) 30/360|
TODAY returns today’s date every time the sheet is loaded or calculated.
WEEKDAY returns the number of the day of the week for the date given. Enter the date-time format for your date. Then select which return type to use. The possible return types are (From Excel 2007 Online Help):
|1 or omitted||Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel.|
|2||Numbers 1 (Monday) through 7 (Sunday).|
|3||Numbers 0 (Monday) through 6 (Sunday).|
WEEKNUM returns the week number for the specified date-time number. It takes in the date in date-time number and a flag to tell the function if you want the weeks to begin on Monday or Sunday. Here is what the return types are and what they mean for this function from Microsoft Excel 2007′s Online Help.
|1||Week begins on Sunday. Weekdays are numbered 1 through 7.|
|2||Week begins on Monday. Weekdays are numbered 1 through 7.|
Returns the date-time number for a day before or after the start date which takes holidays you specify into consideration. Use negative numbers to go to a date before your start date.
Returns the month of a date in date-time format as a number (1-January to 12-December).
HOUR will give you the hour from a date-time number or from the time entered as text.
MINUTE works like HOUR but returns the minutes.
NOW changes its value with the current date and time.
SECOND will return the seconds from either a text representation of the time, or from a date-time code.
TIME will convert text for hour, minute, & seconds to a date-time number and format it with a time format.
TIMEVALUE converts time in a text value to a date-time number.
If you need to work with dates and times in Excel there are many useful tools to work with. The Date-Time format that many of these tools use can be confusing at first but it does not need to be.