Excel Date Functions


Date

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(2012,2,29)

DateValue

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.

=DATEVALUE(“1/1/2012”)

Day

The DAY function takes a Excel date code and gives a number of 1 to 31 to show the date of the month.

=DAY(40909)

Days360

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.

=DAYS360(40909,41274,FALSE)

EDate

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.

=EDATE(40909,-24)

=EDTE(40909,24)

EOMonth

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.

=EOMONTH(40909,-24)

=EOMONTH(40909,24)

NetworkDays

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.

=NETWORKDAYS(40909,41305,40913:40915)

Year

YEAR takes a date-time formatted date and returns its year.

=YEAR(40909)

YearFrac

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
1 Actual/actual
2 Actual/360
3 Actual/365
4 European 30/360

=YEARFRAC(40909, 41305,3)

Today

TODAY returns today’s date every time the sheet is loaded or calculated.

=TODAY()

Weekday

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):

Return_type Number returned
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).

=WEEKDAY(40909,2)

WeekNum

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.

Return_type Week Begins
1 Week begins on Sunday. Weekdays are numbered 1 through 7.
2 Week begins on Monday. Weekdays are numbered 1 through 7.

=WEEKNUM(40909,2)

Workday

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.

=WORKDAY(40972,5,40952:40955)

=WORKDAY(40972,-5,40952:40955)

Month

Returns the month of a date in date-time format as a number (1-January to 12-December).

=MONTH(40972)

Time Functions

Hour

HOUR will give you the hour from a date-time number or from the time entered as text.

=HOUR(“6:40PM”)

=HOUR(0.777778)

Minute

MINUTE works like HOUR but returns the minutes.

=MINUTE(“6:40 PM”)

=MINUTE(0.777778)

Now

NOW changes its value with the current date and time.

=NOW()

Second

SECOND will return the seconds from either a text representation of the time, or from a date-time code.

=SECOND(“6:40:30 PM”)

=SECOND(0.778125)

Time

TIME will convert text for hour, minute, & seconds to a date-time number and format it with a time format.

TimeValue

TIMEVALUE converts time in a text value to a date-time number.

=TIMEVALUE(“6:40 PM”)

Conclusion

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.