Contents
In professional work with spreadsheets, it is not uncommon to interact with dates and times. You won’t be able to do without it. Therefore, God himself ordered to learn how to work with data of this type. This will save you a huge amount of time and prevent a lot of mistakes while working with spreadsheets.
Unfortunately, many beginners do not know how data is processed. Therefore, before considering this class of operations, it is necessary to conduct a more detailed educational program.
How a date is represented in Excel
Date information is processed as the number of days since January 0, 1900. Yes, you are not mistaken. Indeed, from zero. But this is necessary so that there is a starting point, so that January 1 is already considered the number 1, and so on. The maximum supported date value is 2958465, which in turn is December 31, 9999.
This method makes it possible to use dates for calculations and formulas. So, Excel makes it possible to determine the number of days between dates. The scheme is simple: the second is subtracted from one number, and then the resulting value is converted into a date format.
For more clarity, here is a table showing the dates with their corresponding numerical values.
To determine the number of days that have passed from date A to date B, you need to subtract the first from the last. In our case, this is the formula =B3-B2. After entering it, the result is the following.
It’s important to note that the value is in days because we’ve chosen a different format for the cell than date. If we had initially chosen the “Date” format, then the result would have been this.
It is important to pay attention to this point in your calculations.
That is, to display the correct serial number that fully corresponds to the date, you must use any format other than the date. In turn, in order to turn the number into a date, you should set the appropriate format.
How time is represented in Excel
The way time is represented in Excel is slightly different from the date. The day is taken as the basis, and hours, minutes, seconds are its fractional parts. That is, 24 hours is 1, and any smaller value is considered as its fraction. So, 1 hour is 1/24 of a day, 1 minute is 1/1140, and 1 second is 1/86400. The smallest unit of time available in Excel is 1 millisecond.
Similar to dates, this way of representation makes it possible to perform calculations with time. True, there is one thing inconvenient here. After calculations, we get a part of the day, not the number of days.
The screenshot shows the values in the numerical format and the “Time” format.
The method of calculating the time is similar to the date. It is necessary to subtract the earlier time from the later time. In our case, this is the formula =B3-B2.
Since cell B4 first had a General format, then at the end of the introduction of the formula, it immediately changes to “Time”.
Excel, when working with time, performs the usual arithmetic operations with numbers, which are then translated into the time format familiar to us.
Date and time format
As far as we know, dates and times can be stored in different formats. Therefore, you need to know how to enter them correctly so that the formatting is correct.
Of course, you can use the serial number of the day or part of the day when entering the date and time, but this approach is very inconvenient. In addition, you will have to constantly apply a certain format to the cell, which only increases the discomfort.
Therefore, Excel allows you to specify the time and date in different ways. If you apply one of them, then the program immediately converts the information into the appropriate number and applies the correct format to the cell.
See the table below for a list of date and time input methods supported by Excel. The left column lists the possible formats, and the right column shows how they will be displayed in Excel after conversion. It is important to note that if the year is not specified, the current one, which is set in the operating system, is automatically assigned.
In fact, there are much more ways to display. But these are enough. Also, the specific date recording option may differ depending on the country or region, as well as the settings of the operating system.
Custom Formatting
While working with cells, the user can determine what the format will be. He can make it so that only the time, month, day, and so on are displayed. It is also possible to adjust the order in which the date is formulated, as well as the separators.
To access the editing window, you need to open the “Number” tab, where you can find the option “Format Cells” window. In the dialog box that opens, there will be a “Date” category in which you can select the correct date format.
If you select the “Time” category, then, accordingly, a list with options for displaying time will appear.
To apply a specific formatting option to a cell, you must select the desired format and click OK. After that, the result will be applied. If there are not enough formats that Excel offers, then you can find the “All formats” category. There are plenty of options out there too.
If no option is suitable, then it is always possible to create your own. It is very easy to do this. You just need to select the preset formats as a sample and follow these steps:
- Select the cell whose format you want to change.
- Open the “Format Cells” dialog box and find the “Number” tab.
- Next, the category “All formats” opens, where we find the input field “TYPE”. There you need to specify a number format code. After you enter it, click “OK”.
- After these steps, the cell will display date and time information in a custom format.
Using functions with dates and times
When working with dates and times, the user can use more than 20 different functions. And although this amount may be too much for someone, they can all be used to achieve certain goals.
To access all possible functions, you must go to the “Date and Time” category of the “Functions Library” group. We will consider only some of the main functions that make it possible to extract various parameters from dates and times.
YEAR()
Provides the ability to get the year that corresponds to a specific date. As you already know, this value can be between 1900 and 9999.
Cell 1 shows the date in the format DDDD DD.MM.YYYY hh:mm:ss. This is the format we created earlier. Let’s take as an example a formula that determines how many years have passed between two dates.
At the same time, if you look more closely, it turns out that the function did not calculate the completely correct result. The reason is that it only uses dates in its calculations.
MONTH()
With this function, you can highlight the number of the month corresponding to a specific date. Returns a result ranging from 1 to 12. This number in turn corresponds to the number of the month.
DAY()
Similar to the previous functions, this one returns the number of the day in a given date. The calculation result can range from 1 to 31.
TIME()
As the name suggests, this function returns the hour number, which ranges from 0 to 23.
MINUTES()
A function that returns the number of minutes in a specific cell. The possible values that are returned are from 0 to 59.
SECONDS()
This function returns the same values as the previous one, except that it returns seconds.
DAY()
With this function, you can find out the number of the day of the week that is used in this date. Possible values are from 1 to 7, but keep in mind that the countdown starts from Sunday, not Monday, as we usually do.
However, using the second argument, this function allows you to customize the format. For example, if you pass the value 2 as the second parameter, you can set the format so that the number 1 means Monday instead of Sunday. This is much more convenient for the domestic user.
If we write 2 in the second argument, then in our case the function will return the value 6, which corresponds to Saturday.
TODAY()
This function is very simple: no arguments are required for it to work. It returns the serial number of the date that is set on the computer. If it is applied to a cell for which the General format is set, then it will automatically be converted to the “Date” format.
TATA ()
This function also does not require any arguments. It works in the same way as the previous one, only with the date and time. It is used if it is necessary to insert into the cell the current date and time that are set in the computer. And just like in the previous function, when applying this one, the cell is automatically converted to the date and time format, provided that the “General” format was set before.
Both the previous function and this function are automatically changed each time the sheet is recalculated, making it possible to display the most up-to-date time and date.
For example, such a formula can determine the current time.
=TODAY()-TODAY()
In this case, the formula will determine the fraction of a day in decimal format. True, you will have to apply the time format to the cell in which the formula is written, if you want to display exactly the time, and not the number.
THE DATE()
This function has three arguments, each of which must be entered. After calculations, this function returns the serial number of the date. The cell is automatically converted to the “Date” format if it had a “General” format before.
The Day or Month argument can be either positive or negative. In the first case, the date increases, and in the second, it decreases.
You can also use mathematical operations in the arguments of the DATE function. For example, this formula adds 1 year 5 months and 17 days to the date in cell A1.
And such a formula makes it possible to turn a text string into a full-fledged working date, which can be used in other functions.
TIME()
Just like the function THE DATE(), this function has three required parameters – hours, minutes and seconds. After using it, a decimal number will appear in the resulting cell, but the cell itself will be formatted in the “Time” format if it had the “General” format before.
By its principle of operation, the function TIME() и THE DATE() a lot of similar things. Therefore, it makes no sense to focus on it.
It is important to note that this function cannot return a time greater than 23:59:59. If the result is greater than this, the function is automatically reset to zero.
functions THE DATE() и TIME() can be applied together.
In this screenshot, cell D1, which used both of these functions, has a datetime format.
Date and Time Calculation Functions
In total there are 4 functions that allow you to perform mathematical operations with date and time.
DATAMES()
Using this function, you can find out the ordinal number of a date that is behind a known number of months (or ahead of a given one). This function takes two arguments: the start date and the number of months. The second argument can be either positive or negative. The first option must be specified if you want to calculate the future date, and the second – if the previous one.
EOMONTH()
This function makes it possible to determine the ordinal number of the last day of the month that is behind or ahead of a given date. Has the same arguments as the previous one.
WORKDAY()
Same as function DATAMES(), only the delay or advance occurs by a certain number of working days. The syntax is similar.
All three of these functions return a number. To see the date, you need to convert the cell to the appropriate format.
CLEAR()
This simple function determines the number of business days between date 1 and date 2.