Перейти к содержимому

Kutools для Office — один пакет. Пять инструментов. Выполняйте больше.

Как рассчитать процент между двумя датами в Excel?

Author Xiaoyang Last modified

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

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

calculate the percentage between two dates

Рассчитайте процент завершения между двумя датами с помощью формулы

Для расчета процента завершения задачи между двумя датами можно использовать формулу, основанную на функциях DATEDIF и TODAY в Excel. Этот подход подходит, когда у вас небольшое количество задач, и ваш набор данных не меняется часто.

1. Введите следующую формулу в пустую ячейку в строке, где вы хотите видеть результат (например, в ячейке D2):

=(DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1)

Объяснение параметра:
B2 это ячейка с начальной датой задачи, и C2 это ячейка с конечной датой. TODAY() автоматически возвращает сегодняшнюю дату, поэтому формула рассчитывает, сколько дней прошло с момента начала задачи, деленное на полную запланированную продолжительность задачи.

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

3. Выберите все рассчитанные ячейки. Перейдите на вкладку Главная, затем нажмите кнопку Процентный формат в группе Число, чтобы отобразить значения в виде процентов, а не десятичных дробей. Вы можете дополнительно настроить количество десятичных знаков, используя кнопки Увеличить десятичные знаки и Уменьшить десятичные знаки для достижения предпочтительной точности.

format the cell values as percentage format

Предупреждения: Этот подход очень гибкий, но вам нужно убедиться, что все ячейки с датами отформатированы как реальные даты в Excel; в противном случае могут возникнуть ошибки или неверные значения.

Если вы хотите, чтобы результаты автоматически обновлялись каждый день, TODAY() гарантирует, что каждый раз при пересчете книги или ее открытии процент отражает текущий прогресс. Однако учтите, что если вы ссылаетесь на введенные вручную статические значения (вместо TODAY()), вам придется самостоятельно обновлять эти даты по мере их изменения.

Код VBA – Автоматизация расчета процентов для динамического обновления строк и дат

Если ваш рабочий лист содержит много задач с определенными начальными и конечными датами, вы можете использовать VBA для автоматического расчета процента прогресса для каждой задачи на основе сегодняшней даты. Этот метод полезен, когда вы хотите динамически обновлять прогресс задач по строкам без ручного заполнения формул. Макрос проходит через каждую строку, сравнивает сегодняшнюю дату с начальной и конечной датами задачи и рассчитывает прошедшее время в процентах от общей продолжительности. Результат затем вставляется в целевой столбец, предоставляя быстрый обзор того, насколько далеко продвинулась каждая задача.

1. Нажмите Alt + F11, чтобы открыть редактор Microsoft Visual Basic for Applications. В редакторе VBA перейдите к Вставить > Модуль, чтобы создать новый модуль, а затем вставьте следующий код в него:

Sub UpdateTaskCompletionPercent()
'Updated by Extendoffice 20250724
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim StartDateCol As String, EndDateCol As String, PercentCol As String
    Dim startDate As Variant, endDate As Variant, todayDate As Date
    Dim pct As Double
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = Application.ActiveSheet
    
    ' Adjust these column letters as needed
    StartDateCol = "B"
    EndDateCol = "C"
    PercentCol = "D"
    
    todayDate = Date
    lastRow = ws.Cells(ws.Rows.Count, StartDateCol).End(xlUp).Row
    
    For i = 2 To lastRow ' Skip header
        startDate = ws.Cells(i, StartDateCol).Value
        endDate = ws.Cells(i, EndDateCol).Value
        
        If IsDate(startDate) And IsDate(endDate) Then
            If endDate >= startDate Then
                If todayDate < startDate Then
                    pct = 0
                ElseIf todayDate >= endDate Then
                    pct = 1
                Else
                    pct = (todayDate - startDate + 1) / (endDate - startDate + 1)
                End If
                ws.Cells(i, PercentCol).Value = pct
                ws.Cells(i, PercentCol).NumberFormat = "0.00%"
            Else
                ws.Cells(i, PercentCol).Value = "Invalid dates"
            End If
        Else
            ws.Cells(i, PercentCol).Value = "Date error"
        End If
    Next i
End Sub

2. После ввода кода нажмите F5 (или нажмите кнопку Run button Выполнить), чтобы выполнить скрипт. Эта пакетная операция немедленно заполнит столбец с процентами (D) для каждой строки на основе текущих дат.

Примечания и устранение неполадок:

  • Если ваши данные задач находятся в разных столбцах, измените StartDateCol, EndDateCol и PercentCol в коде, чтобы они соответствовали расположению вашего листа.
  • Если скрипт отображает "Ошибка даты" или "Неверные даты", проверьте наличие пустых строк, некорректных записей дат или перекрывающихся/неправильных начальных и конечных дат.
  • Этот подход гарантирует, что ваши расчеты процентов остаются точными и обновленными для всех задач, особенно после массового редактирования данных или при импорте из других источников.

Это решение VBA практично для регулярной подготовки данных и автоматизированных отчетов, где ручное копирование формул неэффективно.

Больше статей о процентах:

  • Расчет исходной цены из процентной скидки
  • Обычно в нашей повседневной жизни есть продукты, которые продаются со скидкой в торговых центрах. Если у вас есть цена со скидкой и процент скидки, как показано на следующем снимке экрана, как можно рассчитать исходную цену этого продукта в Excel?
  • Расчет процента прошедшего года или месяца
  • Предположим, у вас есть список дат в рабочем листе, и вы хотели бы получить процент прошедшего или оставшегося года или месяца на основе заданной даты. Как решить эту задачу в Excel?
  • Использование функции СЧЁТЕСЛИ для расчета процентов в Excel
  • Например, у меня есть сводный отчет по исследовательской работе, и есть три варианта A, B, C, теперь я хочу рассчитать процент каждого из этих трех вариантов. То есть мне нужно знать, какой процент составляет вариант A от всех вариантов. В этой статье я расскажу, как получить процент конкретного варианта в диапазоне.

Лучшие инструменты для повышения производительности Office

Kutools для Excel решает большинство ваших проблем и увеличивает вашу продуктивность на 80%

  • Супер строка формул (легкое редактирование нескольких строк текста и формул); Режим чтения (удобное чтение и редактирование большого количества ячеек); Вставка в отфильтрованный диапазон...
  • Объединение ячеек/строк/столбцов с сохранением данных; Разделение содержимого ячеек; Объединение дублирующихся строк с подсчетом суммы/среднего значения... Предотвращение дублирования ячеек; Сравнение диапазонов...
  • Выбор дублирующихся или уникальных строк; Выбор пустых строк (все ячейки пустые); Супер поиск и нечеткий поиск во многих книгах; Случайный выбор...
  • Точное копирование нескольких ячеек без изменения ссылок на формулы; Автоматическое создание ссылок на несколько листов; Вставка маркеров, флажков и многое другое...
  • Избранные и быстрая вставка формул, диапазонов, диаграмм и изображений; Шифрование ячеек с паролем; Создание списка рассылки и отправка электронных писем...
  • Извлечение текста, добавление текста, удаление по позиции, удаление пробелов; Создание и печать статистики страниц; Преобразование между содержимым ячеек и комментариями...
  • Супер фильтр (сохранение и применение схем фильтрации к другим листам); Расширенная сортировка по месяцу/неделе/дню, частоте и другим параметрам; Специальный фильтр по жирному шрифту, курсиву...
  • Объединение книг и листов; Объединение таблиц на основе ключевых столбцов; Разделение данных на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Группировка сводной таблицы по номеру недели, дню недели и другим параметрам... Отображение разблокированных, заблокированных ячеек разными цветами; Выделение ячеек, содержащих формулы/имена...
kte tab 201905
  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Открывайте и создавайте несколько документов в новых вкладках одного окна, а не в новых окнах.
  • Увеличивает вашу продуктивность на 50% и экономит сотни кликов мышью каждый день!
officetab bottom