Excel – Function for date and time – YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

Last modified date:

There is a huge number of functions for date and time in Excel. they are used to extract parts of the date or time type of cell.

You may have needed to extract some certain parts of the data of date and time data types, for example month. All functions have identical and absolutely simple syntax:

=YEAR(<date>)

=MONTH(<date>)…etc

and the syntax is unchanging for all six functions. So how does it work?

YEAR, MOTNH, DAY – How we work with a cell of date type

We will focus on date first of all. Let’s have a date 15/8/2016 in A4 cell. If we will use formula =YEAR(A4) we get number 2016 as a result. If we type in =MONTH(A4) we get number 8 and finally, yes you guessed it right, for =DAY(A4) number 15 is the result. So each function extracts (drags out) year, month and day from the entered date. Caution, date cannot be entered as a text, as in 15th of August 2016. An error #VALUE would occur for month section.

You can read about how to get rid of error messages in article on IFERROR function usage. Date must contain all three items. Therefore for entry 12.5. the function would again return an error since this entry is not considered to be date data type.

HOUR, MINUTE, SECOND – Cell is of date and time type (or only time)

Time functions work identically. We have time 14:52:11 entered in cell E2. You might be already guessing how will it be. For =HOUR(E2) the result will be number 14, for =MINUTE(E2) you get number 52 and finally for =SECOND(E2) you get 11 seconds, therefore number 11. What happens if we have time data 21:18 for example? No seconds included and we query using function SECOND? The result will not be an error but a number 0. Function simply assumed that if there are not any seconds entered, it is considered to be 0.

One note in the end. All functions mentioned belong to Date and time functions group. Use of these functions is simple and useful whenever we need to extract only some parts out of date and time data.

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Category: Excel functions Tags:

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.