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