Whole course: lesson 1 | lesson 2 | lesson 3 | lesson 4 | lesson 5
Microsoft Excel offers more than 20 different functions for working with dates and times. To some, this amount may seem like a lot, but to someone it will be sorely lacking. Nevertheless, each of the functions offered in Excel will be useful for you and will certainly find its practical application.
All possible functions can be found in the category Date and Time command groups Function Library. In this lesson, we will look at just 7 functions in this category, which allow you to extract various parameters from date and time values, for example, month number, year, number of seconds, etc.
If you have just started working with dates and times in Excel, we recommend that you first turn to the lessons on the basic concepts and formatting of time data.
YEAR()
Returns the year value corresponding to the given date. The year can be an integer between 1900 and 9999.
Cell A1 contains a date in the format DDDD DD.MM.YYYY hh:mm:ss. This is a custom (non-standard) number format for representing dates and times in Excel.
As an example of using the function YEAR, you can give a formula that calculates the number of years between two dates:
If you look closely at the dates, it becomes obvious that the formula does not calculate the correct result. This is because this function discards month and day values, and only operates on years.
MONTH()
Returns the month number corresponding to the given date. The month is returned as an integer in the range 1 to 12.
DAY()
Returns the value of the day corresponding to the given date. The day is returned as an integer in the range 1 to 31.
TIME()
This function returns the hour value corresponding to the given time. The hour is returned as an integer in the range 0 to 23.
MINUTES()
Returns the number of minutes corresponding to the given time. The minutes are returned as an integer in the range 0 to 59.
SECONDS()
Returns the number of seconds corresponding to the given time. The seconds are returned as an integer in the range 0 to 59.
DAY()
This function returns the number of the day of the week corresponding to the given date. The number of the day of the week is returned by default as an integer in the range 1 (Sunday) to 7 (Saturday).
The function has two arguments, the second one is optional. It is a number that determines the type of week countdown. If the second argument is omitted, this is equivalent to the value one. You can see all possible variants of the argument in the figure below:
If as the second argument of the function DAY substitute 2, then the days of the week will correspond to numbers from 1 (Monday) to 7 (Sunday). As a result, the function will return the value 6:
In this lesson, we briefly reviewed 7 Excel functions that allow us to extract the parameters we need from dates and times. In the next tutorial, we will learn how to display dates and times in Excel cells using functions. I hope you found this lesson helpful! All the best to you and success in learning Excel.