How to calculate the date in Excel

The date and time in Excel - there are numbers, formatted in a special way. The date is the integer part of the number, and the time (hours and minutes) is the fractional part.

By default, the number 1 corresponds to the date of 01. 01. 1900 - that is, each date - is the number of days passed from 01. 01. 1900. In this lesson, we will take a closer look at the dates, and in the next lessons – the time.



How in Excel to calculate the days between dates?

Since the date is a number, it means that you can conduct mathematical computing and settlement operations. To count the number of days between two Excel dates does not present any special problems. For a visual example, we first perform the addition, and then subtraction of dates. For this:

  1. On a blank sheet in the cell A1 you need to enter the current date by pressing CTRL +.
  2. In the cell A2 you need to enter an intermediate period in days, for example, 127.
  3. In the cell A3 you need to enter the formula: = A1 + A2.
Since the date is a number.

Note that the «Date» format was automatically assigned to the cell A3. It's not difficult to guess, to calculate the difference in dates in Excel, you need to take away the highest date from the newest date. In the cell B1 you need to enter the formula: A3 - A1. Accordingly, we get the number of days between these two dates.



The calculating age by date of birth in Excel

Now we will learn how to calculate the age by date of birth:

  1. In the new sheet in the cells A1: A3, you need to enter the dates: 18. 04. 1985; 17. 08. 1977; 08. 12. 1992.
  2. In the cells B1 : B3, you need to put the current date.
  3. Now you need to use the function to convert the number of days to the number of years. To do this, you need to enter by manually the following value in the range C1:C3: = DATEDIF(A1; B1; «y»).
calculate the age by date.

Thus, the application of the function allowed us to accurately calculate the age by date of birth in Excel.

Attention! To transform days into years, there is not enough of the formula: Moreover, even if we know that 1 day = 0.0027397260273973 of the year, the formula: =(B1-A1)*0.0027397260273973 will not give us the exact result either.

Days in the years the most accurately to convert the function: = DATEDIF(). You will not find it in the list of the function wizard (SHIFT+F3), but if you simply to enter it into the formula line, it will work.

The DATEDIF function supports to the several parameters:

OptionDescription
«d»Count of full days;
«m»Count of full months;
«y»Count of full years;
«ym»Count of full months without years;
«md»Count of days without months and years;
«yd»Count of days without years.

Let's illustrate the example of using several parameters:

using several parameters.

Attention! That the function: = DATEDIF() worked without errors, make sure that the start date was older than the end date.

The insertion of the date in the Excel cell

The purpose of this lesson is an example of mathematical operations with dates. Also, we'll make sure that for Excel, the date data type is a number.

You need to fill in the table with dates, as shown in the picture:

fill in the table with dates.

There are different ways of entering dates: in the column A there is input method, and in column B there is the display result.

Note that the default format cell is «General», the dates as well as the number are aligned on the right side, and the text on the left one. The value in the cell B4 is recognized by the program as text.

In the cell B7 Excel has assigned by itself to the current year (now is the 2015-th) by default. This is visible when displaying the contents of cells in the formula bar. Notice how the value was originally entered in A7.

The calculating of the Excel date

On the blank sheet in the cells A1 and C1 you need to enter 01. 01. 1900, and in the cells A2 and C2 is 01. 01. 1901. Now we change the format of the cells to «numeric» in the selected range C1:C2. To do this, you can press CTRL + SHIFT + 1.

calculating of Excel date.

C1 contains now the number 1, and C2 - 367. That is, one leap year (366 days) now and one day passed.

The way to display the date can be set using the «Format Cells» dialog box. To call it, press: CTRL + 1. On the «Number» tab, you need to select «Category:» - «Date». In the «Type:» section displays the most popular formats for displaying dates.

display date.

Download examples calculate date in Excel

Read also: The functions for working with dates in Excel

In the next lesson, we will work with the time and the periods of the day on ready-made examples.

All Classes

en ru