Whole course: lesson 1 | lesson 2 | lesson 3 | lesson 4 | lesson 5
Almost any work done in the Excel application contains cells with dates or times. The ability to work with this type of data will allow you to save a lot of time and effort, as well as avoid various errors in the process of working with the program.
Unfortunately, novice Excel users when working with dates and times are faced with a misunderstanding of how Excel handles them. Therefore, before proceeding to consider this issue in more detail, let’s deal with the key points.
Date representation in Excel
In Microsoft Excel, a date is represented by an ordinal number, which is the number of days that have passed since January 0 1900 years. This non-existent date is quite arbitrary and is used in Excel only as a starting point. Ordinal number 1 corresponds to January 1, 1900, number 2 to January 2, 1900, and so on. The maximum date that Excel supports is 31 декабря 9999 года, which corresponds to the ordinal number 2958465.
This way of representation allows you to use dates in Excel formulas. For example, in order to calculate the number of days between two dates, it is enough to subtract the start date from the end date. In a word, working with dates, Excel operates only with numbers, presenting the result to us in certain numerical formats.
In the figure below, cells B2 and B3 represent two dates, while cells C2 and C3 represent the same dates in the format Numerical. I think you have already guessed that these are their serial numbers.
To calculate the number of days between two dates, it is enough to subtract the start date from the end date, i.e. use the formula: =B3-B2. By entering this formula in cell B4, we get the following result:
Note that cell B4 has been applied a format other than Dates, so the value is in days. Otherwise, we would get a different picture:
To display an ordinal number corresponding to a date, you must apply a number format to the cell that is different from the format Dates. And to represent a numeric value in a cell as a date, you need to apply the format to this cell date.
Time representation in Excel
Time in Excel is treated as a fractional part of a day. In other words, Excel takes a day as a unit, and considers all values u1bu1bless than a day (hours, minutes, seconds) as part of the unit. For example, 24 hour is 1/1 of a day, 1140 minute is 1/1 of a day, and 86400 second is 1/XNUMX of a day. The smallest unit of time in Microsoft Excel is one thousandth of a second, i.e. XNUMX millisecond.
As with dates, this approach allows you to use time values in formulas. The only downside is that we usually end up with a fraction of a day as a result, not a number of days (as is the case with date processing). Which is not always convenient.
In the figure below, column B shows the time values in the format Time, and in column C the corresponding fractions of a day in the format Numerical.
If you enter the formula in cell B4 =B3-B2 and press Enter, we get the following result:
Since cell B4 originally had the format General, then after entering the formula, Excel automatically changed the format General on Time. If we apply the format to this cell Numerical, then the result will be:
Working with time, in fact, as with dates, Excel operates only with numbers. As a result, we see the value presented in the time format.
In this lesson, you learned about how Microsoft Excel stores and processes temporary data. As you can see, this has its own specifics and a huge advantage that allows Excel to perform various mathematical operations with dates and times so easily and simply. In the next lesson, you will learn how to enter and format dates and times in Excel. All the best to you and success in learning Excel!