Although it is true, Excel is defined as a program that is part of the Microsoft Office suite , which is a spreadsheet that works as an arrangement of rows and columns divided by boxes in which an infinity of tasks can be executed and actions, such as: recording data, processing text, calculating with mathematical tools, and creating graphics .
Thanks to this, it is a tool that is used to perform all kinds of numerical and mathematical calculations , as well as to support the visualization of data through different types of graphs. Therefore, it is a very useful program for many users with the need to insert data to handle, interpret and store it efficiently .
Now, there are many functions that Microsoft Excel contains , by default. Among them, one of the best references formulas date and time that are within the Library of Formulas of the program . To learn more about what they are about and what they are , we invite you to read this post.
Index:
What are date and time functions and what are they for in Excel spreadsheets?
Basically, the date and time functions are elements that allow users to work with information about seconds, minutes, hours, days, months and years, in any context . So, they are very important for the preparation and development of financial or statistical reports , for example.
In this sense, these functions are used to mark spreadsheets , inserting the current date and time. Like, to decompose a date in order to get the day of the week, month, or year . Additionally, they are ideal patterns to calculate the difference in working days between two dates, search for specific dates, know the current time and many other useful data.
Now, it should be noted that the date and time functions found in Excel are divided into two groups, which are:
- Functions that can be used to add the date, days of the week or time within a certain worksheet .
- Functions that have the ability to calculate the number of days, months, or years between two set dates .
List of all Excel date and time functions and how to use each of them
Given that there are more than 20 date and time functions in Excel , it is essential to know what each of them are and how they should be used correctly, in order to get the most out of them in your spreadsheets.
As a consequence, we proceed to name and explain what they are:
Function: NOW
Specifically, it is a utility that is responsible for returning the current date and time with date and time format, as such . Therefore, through it, users will be able to obtain the real date and time in their Microsoft Excel document .
Therefore, if the cell format was General before specifying this function, the program will modify the cell format to match the date and time format of the locale . Thus, it is useful to present the date and time of the moment , as well as to compute a value based on that data so that said figure is updated each time the person accesses the spreadsheet .
Function: YEAR
This takes care of returning the year of the specified date , as an integer that is between the range 1900 and 9999 . Note that Excel saves dates as sequential serial numbers so they can be used in calculations .
So, for example, the date January 1, January 1900, has the serial number 1 . While, the date January 1, 2008, is the serial number 39448 ; because, it is the one that corresponds to it, by default. On the other hand, the only argument of the function is the date of which you want to know the year .
Function: DAY
As the name implies, this is a function that provides the day of the month for a certain date . In other words, it returns the day number of a specified date and this is expressed as an integer digit that goes from 1 to 31 . Consequently, it only contains one argument consisting of the serial number of the date to be studied .
In this way, said serial number is mandatory so that the Excel system can analyze the information and thus extract the number of the defined day . Even the program’s DAY function can take a date that is stored in another cell as an argument .
Function: WORKDAY
Through this function, you will be able to obtain the serial number of the working day through any specified date . Since, it is responsible for returning a number that represents the number of working days before or after the initial date .
Whereas, working days exclude Saturdays and Sundays (weekends), as well as any holiday date recognized in the argument . Thus, it is considered a very helpful function for any professional or employee who wants to know, specifically, the number of days of work performed .
Function: WORKDAY.INTL
It refers to a utility that returns the serial number of the date before or after a certain number of business days with certain weekend parameters, completely customized . Therefore, with “WORKDAY.INTL” you can obtain the serial number of the working day of a specified date. In order to be able to indicate the days that correspond to the weekend and, by default, the non-working days .
Function: DAYS
Basically, this function deals with showing the number of days between the two set dates . But, it is not available in the following versions of Excel: 2010, 2007 and 2003. Whereas, it is in Microsoft Excel 2013 and 2016, just like in Office 365 .
Now, as an observation, we highlight that, if any argument contains a date in text format, a process similar to the “DATEVALUE” function will be used to see the digit associated with the date. But, if any argument is a text string that cannot be transformed to a valid date in Excel, the function will return the error #VALUE! .
Function: WORKDAYS
What this function does is present the total number of working days between two specific dates . Consequently, the user will be able to obtain in Excel the detailed number of days that he will have to work, during a period of time. So it is really useful .
Function: WORKDAYS.INTL
It was introduced from the 2010 version of Microsoft Excel and, mainly, it is in charge of displaying the number of complete working days between two dates with fully customized weekend parameters. In other words, using this function, you can count how many working days there are between two dates specified by yourself .
Regarding its main advantage, we highlight that it allows you to specify the days of the week that you must exclude from the count or, failing that, that are considered weekends, as well as working vacation days. Likewise, it is characterized by containing four very specific arguments .
Function: DIAS360
Specifically, this function has the ability to calculate or measure the number of days between two dates , based on a 360-day year, by default. As a result, you will get the corresponding number of days using a 360-day year as the pattern.
Function: WEEKDAY
It is a very simple function, since it simply returns the number from 1 to 7 that identifies the day of the week , as such. Thus, as a result, you will get the day of the week from a date set in the syntax. In this case, each day has its specific digit : Monday (1), Tuesday (2), Wednesday (3), Thursday (4), Friday (5), Saturday (6) and Sunday (7). Although, the count can also start from Sunday with the number 1 and, in effect, end on Monday with the number 7.
Function: DATE
It is a utility that allows you to obtain the serial number that represents a certain date , specifically, in Microsoft Excel date and time code . To do this, it is necessary to enter the day, month and year , accordingly. It should be noted that the DATE function is of great help to transform certain data into a date format , in case the day, month and year are presented in separate columns. After converting to the appropriate data type, it will be possible to perform operations with the dates, without any limitation .
Function: DATE.MONTH
In this case, the function displays the serial digit of the date that corresponds to the indicated number of months before or after the start date . Taking into account that, said date is one or more months before or after the indicated date.
Function: DATEVALUE
It is simply a function that converts a date formatted text to a date serial number which of course symbolizes the data in Excel date and time code .
Function: MONTH END
Con esta función, las personas que utilizan Excel pueden obtener el número de serie del último día del mes, antes o después de la cantidad de meses señalada. Así, es totalmente útil para tomar dicha fecha como base para algún cálculo o, específicamente, para medir los días faltantes.
Función: FRAC.AÑO
Muchos usuarios de Excel, necesitan ciertos datos para realizar cálculos específicos en este programa y muchos de esos parámetros, tienen que estar expresados en fracciones. Por esto, existe una función que se ocupa de devolver la fracción del año que representa la cantidad de días completos en un rango comprendido entre la fecha inicial y la fecha final. Es decir que, tiene la capacidad de computar la fracción de año que simboliza el número de días entre dos fechas especificadas.
Función: HORA
Specifically, this is a function of time and as a consequence, it has the ability to punctuate and present the time as a number from 0 to 23, that is, in military format . Thus, for example, the number 0 corresponds to 12:00 am, while the number 23 represents 11:00 pm Which is shown from a detailed time value.
Function: TIME NUMBER
This is a utility that transforms one hour of text into a Microsoft Excel serial number for one hour . This means that it is the function responsible for formatting the digit that contains a time setting, just after entering the formula .
Function: TODAY
This is a utility that returns the current date in date format . Although it may seem very simple, the truth is that it helps to calculate certain data that are complicated. An example of this lies in measuring age using Excel , since this data depends entirely on the current day and thus, the most successful solution is based on using the TODAY function .
Function: ISO.NUMBER OF WEEK
It is classified as one of the most modern functions of Excel and, therefore, it is only available in the 2013, 2016 and Office 365 versions . Basically, it deals with presenting the ISO week number of the year for a specified date .
Now, limiting some relevant observations about ISO.NUM.DE.SEMANA , we emphasize that if the date has a numerical value outside the range of valid dates of the program, the function will return the error “#NUM!” . Also, if the argument does not have a valid date type, the function will return the error “#VALUE!” .
Function: MONTH
Within Microsoft Excel , this function allows you to get the specific number of the month from a given date . Therefore, the result will be an integer that goes from 1 to 12 . That is, from January to December, correspondingly. Thus, once you specify a date, the program will return the appropriate month .
Function: MINUTE
It is also considered a function of time in Excel, since it provides the punctuated minute of any hour value entered . Consequently, the result obtained will be an integer in the range 0 to 59 . It should be noted that this function of the Microsoft spreadsheet program is ideal for working with time information , in order to compute a certain value extracted from one hour.
Function: NSTIME
By means of this Excel utility , you will be able to obtain the serial number that symbolizes a certain time . Since, it takes care of converting hours, minutes and seconds given as numbers, into a single digit of the program series . For that, of course, it requires three fundamental parameters: hour, minute and second.
Function: WEEK NUMBER
It is a formula that, as a result, returns the corresponding week number within a year , according to a date indicated in the pattern. Which, it does based on the fact that a year contains a total of 52 weeks .
Now, although it seems to be a direct calculation, it is important to take into account certain considerations regarding the operation of this utility . Since, “WEEKNUMBER” considers that the first week of the year is the one in which the 1st of January is found . Also, by default, it defines Sunday as the start of the week , by default.
Function: SECOND
It is another of the time functions that you can find as a date and time formula in Microsoft Excel . Which provides the second of the time value expressed in the syntax . Thus, as a result, it presents an integer that goes from 0 to 59 .