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

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

На следующем снимке экрана показан пример списка задач с указанием Дата начала, Конечная дата и требуемого рассчитанного процента завершения:

• Расчёт процента завершения между двумя датами с помощью формулы
• Код VBA — автоматизация расчёта процента с динамическим обновлением по строкам и датам

---

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

Чтобы рассчитать процент завершения задачи между двумя датами, используйте формулу на основе функций Excel РАЗНДАТ и СЕГОДНЯ. Этот подход идеален, если у вас немного задач и данные редко обновляются.

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

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

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

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

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

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

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

---

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

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

1. Нажмите Alt + F11, чтобы открыть редактор Microsoft Visual Basic для приложений. В редакторе 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(или кнопку) Выполнить), чтобы рассчитать процент завершения для каждой строки на основе текущих дат.

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

• Если данные о задачах расположены в разных столбцах, измените в коде параметры StartDateCol, EndDateCol и PercentCol, чтобы они соответствовали структуре вашего листа.
• Если скрипт выводит сообщение «Date error» или «Invalid dates», проверьте наличие пустых строк, некорректных записей дат или перекрывающихся/неправильных начальной и конечной дат.
• Такой подход обеспечивает точность и актуальность расчётов процента завершения для всех задач — особенно после массового редактирования данных или импорта из других источников.

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

---

---

Другие статьи по расчёту процентов: