# How to calculate the percentage of time elapsed between two dates in Excel

Divide the days so far by the total days and clamp to 0–100%: `=MIN(1,MAX(0,($G$1-B2)/(C2-B2)))`.

**Excel:** `=MIN(1,MAX(0,($G$1-B2)/(C2-B2)))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Project | Start | End | Time elapsed |  | As of | 2026-10-29 |
| Website redesign | 2026-09-01 | 2026-12-18 |  |  |  |  |
| ERP rollout | 2026-01-05 | 2026-12-31 |  |  |  |  |
| Brand refresh | 2026-11-02 | 2027-01-29 |  |  |  |  |
| Office move | 2026-08-03 | 2026-10-23 |  |  |  |  |
| Q4 campaign | 2026-10-01 | 2026-12-31 |  |  |  |  |

Formula in D2, filled to D6: `=MIN(1,MAX(0,($G$1-B2)/(C2-B2)))`

Result: 53.7%; 82.5%; 0.0%; 100.0%; 30.8%

## How it works

`$G$1-B2` is the days since the start and `C2-B2` the length of the project, so their ratio is the share of the schedule used. MAX and MIN clamp it to 0% before the start and 100% after the end: the brand refresh hasn't started and the office move is over.

## Other ways

- `=MEDIAN(0,1,($G$1-B2)/(C2-B2))`: MEDIAN of 0, 1 and the ratio clamps it in one function.
- `=($G$1-B2)/(C2-B2)`: Without the clamp. (Shows a negative value and more than 100% for projects not started or finished.)

## Common mistakes

- If the start and end are the same day, the ratio divides by 0. Guard it with `IF(C2=B2,…)`.
- Time elapsed is not progress. Compare it with work completed to spot projects that are slipping.
- Without the cap, projects that are over or not started show values outside 0% to 100%, which breaks progress bars.

## Questions

**What percent of the year has passed?**

`=($G$1-DATE(YEAR($G$1),1,0))/(DATE(YEAR($G$1),12,31)-DATE(YEAR($G$1),1,0))`.

**How do I count working days instead?**

Divide NETWORKDAYS: `=NETWORKDAYS(B2,$G$1)/NETWORKDAYS(B2,C2)`, clamped the same way.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/percentage-of-time-elapsed-between-two-dates