How to calculate the percentage between two dates in Excel?

When managing ongoing projects or monitoring the progress of scheduled tasks in Excel, it is often necessary to calculate how much of a task’s duration has elapsed based on today’s date. This allows you to evaluate whether projects are ahead, on track, or behind schedule by expressing the elapsed time as a percentage of the total planned duration. For example, you might have a worksheet with tasks, each with their own start and end date. You’ll want to know, as of today, what percent of the scheduled period for each task has passed. This tutorial will walk you through practical methods to achieve this in Excel, outline ways to automate updating progress percentages, and show how to visually highlight tasks based on these results using conditional formatting.

The following screenshot shows a sample task list with a start date, end date, and the desired calculated percentage of completion:

• Calculate the percentage of completion between two dates with formula
• VBA Code – Automate percentage calculation for dynamic update across rows and dates

Calculate the percentage of completion between two dates with formula

For calculating the percentage of completion of task between two dates, you can use a formula based on Excel’s DATEDIF and TODAY functions. This approach is suitable when you have a small number of tasks and your data set does not change frequently.

1. Enter the following formula into a blank cell in the row where you want to see the result (for example, in cell D2):

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

Parameter explanation:
B2 is the cell with the start date of the task, and C2 is the cell with the end date. TODAY() automatically returns today’s date, so the formula calculates how many days have passed since the task started, divided by the task’s full scheduled length.

2. After you have entered the formula, drag the fill handle down to copy the formula for all necessary rows. Each row will then show its task’s current completion based on today’s date. The example below demonstrates this applied to several tasks:

3. Select all the calculated cells. Go to the Home tab, then click the Percentage Style button in the Number group to display the values as percentages rather than decimals. You can further adjust the number of decimal places using the Increase Decimal and Decrease Decimal buttons for preferred precision.

Cautions: This approach is very flexible, but you need to ensure all date cells are formatted as actual dates in Excel; otherwise, errors or incorrect values may occur.

If you want the results to update automatically each day, TODAY() ensures that every time the workbook is recalculated or opened, the percentage reflects the current progress. However, beware that if you reference manually entered static values (instead of TODAY()), you will need to update those dates yourself as time passes.

VBA Code – Automate percentage calculation for dynamic update across rows and dates

If your worksheet contains many tasks with defined start and end dates, you can use VBA to automatically calculate the progress percentage for each task based on today's date. This method is helpful when you want to dynamically update task progress across rows without manually filling formulas. The macro loops through each row, compares today's date with the task's start and end dates, and calculates the elapsed time as a percentage of the total duration. The result is then inserted into a target column, providing a quick overview of how far along each task is.

1. Press Alt + F11 to open the Microsoft Visual Basic for Applications editor. In the VBA editor, go to Insert > Module to create a new module, and then paste the following code into it:

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. After entering the code, press F5 (or click the Run button) to execute the script. This batch operation will immediately fill the percentage column (D) for each row based on current dates.

Notes and troubleshooting:

• If you keep your task data in different columns, adjust StartDateCol, EndDateCol, and PercentCol in the code to match your sheet layout.
• If the script displays "Date error" or "Invalid dates", check for blank rows, incorrect date entries, or overlapping/incorrect start-end dates.
• This approach ensures your percentage calculations are kept accurate and updated for all tasks, especially after bulk data edits or when importing from other sources.

This VBA solution is practical for recurring data preparations and automated reporting where copying formulas manually is inefficient.

More relative percentage articles:

---

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
• Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
• Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
• Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...

Read More... Full Features 30-Day Free Trial... Purchase... 

---

Office Tab - brings tabbed interface to Office, and make your work much easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Read More... Full Features 30-Day Free Trial... Purchase...