Если у вас есть лист Excel^{(Excel sheet)} с большим количеством дат, скорее всего, вам в конечном итоге потребуется вычислить разницу между некоторыми из этих дат. Может быть, вы хотите узнать, сколько месяцев вам понадобилось, чтобы погасить свой долг, или сколько дней вам понадобилось, чтобы похудеть?

Вычислить разницу между датами в Excel легко, но это может усложниться в зависимости от того, как вы хотите вычислить определенные значения. Например, если вы хотите узнать количество месяцев между 05.02.2016 и 15.01.2016, ответ должен быть 0 или 1? Некоторые люди могут сказать 0, так как это не полный месяц между датами, а другие могут сказать 1, потому что это другой месяц.

В этой статье я покажу вам, как рассчитать разницу между двумя датами, чтобы найти количество дней, месяцев и лет с помощью различных формул, в зависимости от ваших предпочтений.

Дней между двумя датами

Самый простой расчет, который мы можем сделать, — это получить количество дней между двумя датами. Преимущество расчета дней в том, что на самом деле существует только один способ расчета значения, поэтому вам не нужно беспокоиться о том, что разные формулы дают разные ответы.

В моем примере выше у меня есть две даты, хранящиеся в ячейках A2 и A3^{(A2 and A3)} . Справа вы можете видеть, что разница между этими двумя датами составляет 802 дня. В Excel всегда есть несколько способов вычислить одно и то же значение, что я и сделал здесь. Давайте посмотрим на формулы:

Первая формула представляет собой простое вычитание двух дат, A3 – A2 . Excel знает, что это дата, и просто вычисляет количество дней между этими двумя датами. Легко^(Easy) и очень прямолинейно. Кроме того, вы также можете использовать функцию ДНИ .^(DAYS)

=DAYS(A3, A2)

Эта функция принимает два аргумента: дату окончания^{(end date)} и дату начала^{(start date)} . Если вы поменяете даты в формуле, вы просто получите отрицательное число. Наконец, вы можете использовать функцию DATEDIF , которая включена в Excel из Lotus 1-2-3 days, но не является официально поддерживаемой формулой в Excel .

=DATEDIF(A2, A3, "D")

Когда вы вводите формулу, вы увидите, что Excel не предлагает никаких предложений для полей и т. д. К счастью, здесь вы можете увидеть синтаксис и все поддерживаемые аргументы для функции DATEDIF^{(DATEDIF function)} .

В качестве дополнительного бонуса, что, если вы хотите рассчитать количество рабочих дней между двумя датами? Это достаточно просто еще и потому, что у нас есть встроенная формула:

=NETWORKDAYS(startDate,endDate)

Посчитать количество дней и дней недели достаточно просто, поэтому поговорим сейчас о месяцах.

Месяцы между двумя датами

Самый сложный расчет — количество месяцев, потому что вы можете либо округлить, либо округлить в меньшую сторону в зависимости от того, полный это месяц или неполный. Теперь в Excel есть ^(Excel)функция МЕСЯЦЕВ^{(MONTHS function)} , но она очень ограничена, потому что при расчете разницы она будет смотреть только на месяц, а не на год. Это означает, что его можно использовать только для расчета разницы между двумя месяцами одного и того же года.

Поскольку это бессмысленно, давайте посмотрим на некоторые формулы, которые дадут нам правильный ответ. Microsoft предоставила их здесь^(here) , но если вам лень переходить по ссылке, я также предоставил их ниже.

Round Up - =(YEAR(LDate)-YEAR(EDate))*12+MONTH(LDate)-MONTH(EDate)

Round Down - =IF(DAY(LDate)>=DAY(EDate),0,-1)+(YEAR(LDate)-YEAR(EDate))
*12+MONTH(LDate)-MONTH(EDate)

Теперь это две довольно длинные и сложные формулы, и вам действительно не нужно понимать, что происходит. Вот формулы в Excel:

Обратите внимание, что вы должны отредактировать формулу округления вниз в самой ячейке, потому что по какой-то странной причине вся формула не отображается в строке формул^{(formula bar)} . Чтобы увидеть формулу в самой ячейке, перейдите на вкладку « Формулы^(Formulas) » и нажмите « Показать формулы»^{(Show Formulas)} .

Итак, каков окончательный результат двух формул в нашем текущем примере? Давайте посмотрим ниже:

Округление^(Round) вверх дает мне 27 месяцев, а округление вниз дает мне 26 месяцев, что является точным, в зависимости от того, как вы хотите посмотреть на это. Наконец, вы также можете использовать функцию DATEDIF^{(DATEDIF function)} , но она вычисляет только полные месяцы, поэтому в нашем случае она возвращает ответ 26.

=DATEDIF(A2, A3, "M")

Годы между двумя датами

Как и в случае с месяцами, годы также могут быть рассчитаны в зависимости от того, хотите ли вы считать 1 год как полные 365 дней или учитывается изменение года. Итак, давайте возьмем наш пример, в котором я использовал две разные формулы для расчета количества лет:

В одной формуле используется РАЗНДАТ^(DATEDIF) , а в другой — функция ГОД^{(YEAR function)} . Поскольку разница в количестве дней составляет всего 802, функция РАЗНДАТ^(DATEDIF) показывает 2 года, тогда как функция ГОД^{(YEAR function)} показывает 3 года.

Опять же, это вопрос предпочтений и зависит от того, что именно вы пытаетесь рассчитать. Полезно знать оба метода, чтобы решать разные задачи с помощью разных формул.

Если вы внимательно относитесь к используемым формулам, довольно легко вычислить именно то, что вы ищете. Есть также гораздо больше функций даты, помимо тех, которые я упомянул, поэтому не стесняйтесь проверять их также на сайте поддержки Office^{(Office Support)} . Если у вас есть какие-либо вопросы, не стесняйтесь комментировать. Наслаждаться!

How to Subtract Dates in Excel

If you have an Excel sheet with a lot of dates, chances arе yoυ will eνentually need to calculate the diffеrences between some of those dates. Maybe you want to see how many months it took yоu tо pay off your debt or how many days it took you to lose a certain amount of weight?

Calculating the difference between dates in Excel is easy, but can become complicated depending on how you want to calculate certain values. For example, if you wanted to know the number of months between 2/5/2016 and 1/15/2016, should the answer be 0 or 1? Some people might say 0 since it’s not a full month between the dates and others might say 1 because it’s a different month.

In this article, I’ll show you how to calculate the differences between two dates to find the number of days, months and years with different formulas, depending on your preferences.

Days Between Two Dates

The simplest calculation we can do is to get the number of days between two dates. The good thing about calculating days is that there really is only one way to calculate the value, so you don’t have to worry about different formulas giving you different answers.

In my example above, I have two dates stored in cells A2 and A3. At the right, you can see the difference between those two dates is 802 days. In Excel, there are always multiple ways to calculate the same value and that’s what I did here. Let’s take a look at the formulas:

The first formula is just a simple subtraction of the two dates, A3 – A2. Excel knows it’s a date and simply calculates the number of days between those two dates. Easy and very straight-forward. In addition, you can also use the DAYS function.

=DAYS(A3, A2)

This function takes two arguments: the end date and the start date. If you switch the dates in the formula, you’ll just get a negative number. Lastly, you can use a function called DATEDIF, which is included in Excel from Lotus 1-2-3 days, but isn’t an officially supported formula in Excel.

=DATEDIF(A2, A3, "D")

When you type the formula, you’ll see that Excel does not give you any suggestions for the fields, etc. Luckily, you can see the syntax and all supported arguments for the DATEDIF function here.

As an added bonus, what if you wanted to calculate the number of weekdays between two dates? That’s easy enough also because we have a built-in formula:

=NETWORKDAYS(startDate,endDate)

Calculating the number of days and weekdays is simple enough, so let’s talk about months now.

Months Between Two Dates

The trickiest calculation is the number of months because of how you can either round up or round down depending on whether it’s a complete month or a partial month. Now there is a MONTHS function in Excel, but it’s very limited because it will only look at the month when calculating the difference and not the year. This means it’s only useful for calculating the difference between two months in the same year.

Since that is kind of pointless, let’s look at some formulas that will get us the correct answer. Microsoft has provided these here, but if you are too lazy to visit the link, I have provided them below also.

Round Up - =(YEAR(LDate)-YEAR(EDate))*12+MONTH(LDate)-MONTH(EDate)

Round Down - =IF(DAY(LDate)>=DAY(EDate),0,-1)+(YEAR(LDate)-YEAR(EDate))
*12+MONTH(LDate)-MONTH(EDate)

Now these are two fairly long and complicated formulas and you really don’t need to understand what’s going on. Here are the formulas in Excel:

Note the you should edit the round down formula in the actual cell itself because for some odd reason the entire formula does not show up in the formula bar. In order to see the formula in the cell itself, click on the Formulas tab and then click Show Formulas.

So what’s the final result of the two formulas on our current example? Let’s see below:

Round up gives me 27 months and round down gives me 26 months, which is accurate, depending on how you want to look at it. Lastly, you can also use the DATEDIF function, but it only calculates full months, so in our case the answer it returns is 26.

=DATEDIF(A2, A3, "M")

Years Between Two Dates

As with months, years can also be calculated depending on whether you want to count 1 year as a full 365 days or if a change in the year counts. So let’s take our example where I have used two different formulas for calculating the number of years:

One formula uses DATEDIF and the other uses the YEAR function. Since the difference in the number of days is only 802, DATEDIF shows 2 years whereas the YEAR function shows 3 years.

Again, this is a matter of preference and depends on what exactly you are trying to calculate. It’s a good idea to know both methods so that you can attack different problems with different formulas.

As long as you are careful with which formulas you use, it’s fairly easy to calculate exactly what you are looking for. There are also a lot more date functions outside of the ones I mentioned, so feel free to check those out also on the Office Support site. If you have any questions, feel free to comment. Enjoy!

Related posts

• Добавить линейный Regression Trendline на Excel Scatter Plot

• Как сделать Histogram в Excel

• Как надежно Password Protect Excel File

• Как создать Flowchart в Word and Excel

• Как Create Labels в Word от Excel Spreadsheet

• Как записать Macro в Excel

• Как Group Worksheets в Excel

• Автоподбор ширины столбцов и высоты строк в Excel

• Как добавить планки погрешностей в Excel

• Как использовать функции автоматического восстановления и резервного копирования Excel

• Как найти совпадающие значения в Excel

• Как использовать функцию YEARFRAC в Excel

• Как Automatically Backup A Word Document в OneDrive

• Вставить лист Excel в документ Word

• Как создать Distribution List в Outlook

• Как создать Drop Down List в Excel

• Как использовать функцию ВПР в Excel

• Как создать Gantt Charts в Microsoft Excel

• Как понять анализ «что, если» в Microsoft Excel

• Как удалить, взломать или взломать забытый пароль Excel XLS