In this free Excel help article we will look at some more of the Microsoft Excel date functions. To this list we will also add some of the Microsoft Excel time functions too as they both go hand-in-hand.

EOMONTH  – The function stands for End Of the Month. It is a very useful Excel function and we’ll explain its uses with some real time scenarios.

As the name suggests EOMONTH or End of the Month returns the date of the last day of the month specified in a date. The syntax is =EOMONTH(start_date, months).  For example =EOMONTH(
“2/2/2011”,0), here 0 specifying the current month, computes the last date as “2/28/2011”. Note that the function automatically takes care of leap years. You can also use a cell if that cell contains the date that you want to use inside the MS Excel EOMONTH function.

You can also find out the last date of next or subsequent months or previous months from a date. To do this add or subtract the month or number of months from the date you are using.

Here are some examples.

EOMONTH(“2/28/2011”, 1) will return the date 3/31/2011.

EOMONTH(“2/28/2011”, -1) will return the date 1/31/2011.

EOMONTH(“2/28/2011”, 2) will return the date 4/30/2011.

EOMONTH(“2/28/2011”, -2) will return the date 12/31/2010.

MINUTE – This function returns the minute component of a time. The usage is =MINUTE(time). Using an example if C1 were to have the time 23:47:20 (i.e. 23 hours, 47 minutes and 20 seconds) in it, =MINUTE(C1) will return 47.

HOUR – This Excell function returns the hour component of a time. The usage is =HOUR(time). Using an example if C1 were to have the time 23:47:20 (i.e. 23 hours, 47 minutes and 20 seconds) in it, =HOUR(C1) will return 23.

SECOND – This Microsoft function returns the hour component of a time. The usage is = SECOND(time). Using an example if C1 were to have the time 23:47:20 (i.e. 23 hours, 47 minutes and 20 seconds) in it, =SECOND (C1) will return 20.

NETWORKDAYS –  This is a good one for Excel consultants. You want to learn this one. As the name indicates it stands for net working days. The syntax is =NETWORKDAYS(start_date, end_date, holidays). This function is particularly useful in payroll. All we need to provide is a start-date, end-date and the list of holidays between those two dates and the net working days are instantly calculated. Both Saturday and Sunday are considered as holidays automatically.

WORKDAY – A real lifesaver of a workbook function that computes a future date based on a start-date, how many days to advance excluding weekends and holidays of the institution. A very useful function used particularly to calculate a student’s evaluation date. The format is =WORKDAY(start-date, days, holidays) and bingo you have the result of the evaluation date.

TIME – Enter a cell with time in it or enter a time into the function as =TIME(23,12,12) and it will return 11:12 PM for time. The three parameters for time in order are Hours, Minutes and Seconds.

TIMEVALUE –   This simple excel function converts a time in text format to a number which can be formatted to reveal time.  For example =TIMEVALUE(“11:15:15”) converts to the number 0.468923611. Format this cell to display the contents as a time and it will display 11:15 AM.

WEEKDAY – This function returns a number for the day of the week in a date. Starting Sunday with 1 till Saturday which is 7, this function returns a number. Using a table and in combination with VLOOKUP discussed in an earlier article you can even return a weekday by its name.

WEEKNUM – This function returns the week number i.e. the number of the week of that year the date falls in. Using this date function is pretty straight forward as in =WEEKNUM(“5/9/2011”) which will return 20 as this dates falls in the 2o’th week of 2011.

YEARFRAC – This final Excel date function Returns the number of years from a start-date to an end-date. This date function is particularly useful to calculate the years worked by an employee, calculating ages using a person’s date of birth to just name a few. The result will have decimals but using the INT function will return the integer part of the result. Example = YEARFRAC(B22, C22)  where B22 and C22 contain the start-date and end-date respectively. To extract the integer part use the INT function as follows =INT(YEARFRAC(B22, C22)).