Contents
Whole course: lesson 1 | lesson 2 | lesson 3 | lesson 4 | lesson 5
We continue to study the category functions Date and Time in Excel. If you have not read our last lesson on functions, we recommend you to refer to it. In this tutorial, we will get acquainted with 4 very useful functions that allow you to not only display, but also create dates and times in Excel.
TODAY()
It takes no arguments and returns the ordinal number of the current date, which is taken from your computer’s system time. If the function is applied to a cell with the format General, then the format will be applied to the result date.
TATA ()
It takes no arguments and returns the ordinal number of the current date and time, which are taken from the system settings of your computer. If the function is applied to a cell with the format General, the date and time format will be applied to the result.
Function Results TODAY() и The TDATA change each time the Excel worksheet is recalculated.
An example is a formula that calculates the current time in Excel:
=DATE()-TODAY() – in this case, the formula will return a decimal number (a fraction of a day). To see the result, as in the figure below, you need to apply the time format to cell A1.
THE DATE()
It takes three required arguments (year, month, and day) and returns a serial number for the date. If before entering this function the cell had the format General, the result will be formatted as a date.
Argument value Day (or Month) can be either positive or negative. If the value of the argument exceeds the number of days in the current month (the number of months in a year), then the date is incremented:
If the argument value is a negative number, then the date is reduced:
Function arguments DATE may contain mathematical operations as well as other Excel functions. For example, to add 1 year 5 months and 17 days to a specific date, you can use the following formula:
And to convert a text string to a date, use this formula:
TIME()
It takes three required arguments (hours, minutes, seconds) and returns a decimal number, i.e. fraction of the day. If before entering this function the cell had the format General, then the result will be formatted as a time.
Function TIME very similar to DATEso we won’t focus on it.
It is important to note that the function TIME cannot return a value greater than 23:59:59. When this value is reached, the function is reset to zero.
functions DATE и TIME can be used together:
In this example, the date and time format is applied to cell D1.
This concludes our lesson. Today we have analyzed several useful functions from the category Date and Timewhich will definitely come in handy. I hope you enjoyed this lesson. In the next lesson, we will cover the last topic regarding working with dates and times in Excel, and we will talk about various calculations using functions. All the best to you and success in learning Excel.