To calculate the middle date between two dates in Excel, put the earlier or later date in A2 and the other date in B2, then enter =AVERAGE(A2,B2) in C2. Format C2 as a date; for example, the midpoint between April 10 and April 20, 2026 is April 15, 2026.
The simplest formula for a midpoint date
Excel stores dates as serial numbers, so finding the midpoint is a matter of averaging the two date values. The formula works whether A2 contains the earlier date or the later one. It also works across months and years: Excel calculates the distance between the underlying values rather than trying to average the month and day separately.
For example, with April 10, 2026 in A2 and April 20, 2026 in B2, the dates are 10 days apart. Half that interval is 5 days, so the midpoint is April 15. The result is a numeric date value, not text, meaning you can use it in date calculations, sort it chronologically, or apply date formatting.
You can also calculate the same midpoint by adding half the difference between the dates to the first date. With A2 holding April 10, 2026 and B2 holding April 20, 2026, enter =A2+(B2-A2)/2 in C2; the result is April 15, 2026. This version makes the interval logic explicit, while AVERAGE is usually easier to read.
What “middle date” means
For two endpoints, the midpoint normally means the point exactly halfway through the elapsed time from one date to the other. It is not necessarily the middle date in a list of dates that includes both endpoints. That distinction matters when the dates are an odd number of days apart, because the exact midpoint falls between two calendar dates.
Suppose a range starts on April 10 and ends on April 13. There are three elapsed days between the endpoints, so the exact midpoint is April 11 at noon. But the inclusive list April 10, 11, 12, and 13 has four dates and therefore has no single central member. Depending on the task, a person might choose April 11 or April 12 as the central whole date. Decide which meaning fits before rounding.
For an interval with an even number of elapsed days, the midpoint lands on a whole date. April 10 to April 20 has a 10-day difference, so the midpoint is five days after the start. The number of dates counted inclusively is 11, which does have a central date: April 15.
How Excel handles a half-day result
When the two dates are one day apart, the exact midpoint is halfway through a day. Excel can store that fractional part, even if the cells contain only dates. For example, the average of April 10 and April 11 is April 10 at 12:00 noon. If the result cell is formatted to show only a date, Excel hides the time portion and displays April 10. The underlying value is still midday.
If your answer must be a whole calendar date, choose a rounding rule rather than relying on display formatting. With A2 containing April 10, 2026 and B2 containing April 11, 2026, enter =ROUND(AVERAGE(A2,B2),0) in C2 to round the midpoint to the nearest day. Excel rounds this half-day result forward to April 11.
To keep the earlier calendar date in that same example, use =ROUNDDOWN(AVERAGE(A2,B2),0) in C2, assuming A2 and B2 contain those dates. The result is April 10. ROUNDDOWN is useful when you explicitly want to discard the time fraction; it is not a universally correct choice. If your reporting rules specify the later date, earlier date, or nearest date, follow that rule consistently.
Enter and format the dates correctly
Excel must recognize the inputs as dates, not as text that merely looks like a date. Type dates in a format Excel recognizes under your regional settings, or use the date picker or a date-formatted cell. A quick check is to select a date cell and change its number format: if it can display as a different date style, it is likely a numeric date value. Text dates can cause unexpected calculations or errors.
To format the midpoint, select C2 and choose Home > Number Format, then select Short Date or Long Date. You can also use the Format Cells dialog: press Ctrl+1 in Excel for Microsoft 365 on Windows, then select a date format. On Mac, use Command+1 to open Format Cells. Formatting changes how the value looks, not the underlying midpoint or its precision.
If a date appears as a number such as 46127, the calculation may be correct: Excel is showing the date serial value because the result cell is formatted as General or Number. Apply a date format rather than changing the formula. If the cell displays ####, widen the column; that display often means the column is too narrow for the formatted value.
Keep the result blank until both dates are entered
A simple average can produce a misleading result if one input is missing. AVERAGE ignores a genuinely empty referenced cell, so if only one date is present, it may return that date rather than signaling that the pair is incomplete. For a worksheet where dates will be filled in later, a blank guard makes the intended behavior clearer.
With the two required dates in A2 and B2 and the result in C2, enter =IF(COUNT(A2:B2)<2,"",AVERAGE(A2,B2)) in C2. It stays blank until both cells contain numeric values, then returns their midpoint. COUNT also excludes text, so this prevents text entries from being treated as valid dates. Format C2 as a date as you would for the basic formula.
This check confirms that both cells contain numbers, not that they contain sensible dates. A mistyped number can still be counted. For a shared or important workbook, use clear labels, data validation, and a reasonable date range so that incorrect inputs are less likely. If a formula result is blank unexpectedly, check that both inputs are actual Excel dates and not text or formulas returning an empty string.
Use the midpoint formula down a list
For many date pairs, put each start date and end date on the same row, then calculate one midpoint per row. For example, place starts in A2:A20, ends in B2:B20, and results in C2:C20. Enter =AVERAGE(A2,B2) in C2 and fill the formula down. Excel adjusts the row references for each pair, so C3 uses A3 and B3, and so on.
In an Excel table, a calculated column can make the formula easier to understand. If the table columns are named Start Date and End Date, the midpoint column can use =AVERAGE([@[Start Date]],[@[End Date]]). Enter it in the first data row of that column; Excel tables generally extend a calculated-column formula to the other rows. The table formula refers to the current row, reducing the risk of accidentally pairing dates from different records.
Before filling a formula down, confirm that each row contains the intended matching pair. A correct formula cannot detect that a start date from one project was paired with an end date from another. Add a descriptive heading such as Midpoint Date, and keep the source dates visible so readers can check the result.
Dates that include times
The same calculation works when the cells contain both a date and a time. Excel represents the date as the whole-number portion of the value and the time as a fraction of a day, so averaging timestamps gives the midpoint in elapsed time. For example, halfway between 9:00 a.m. and 3:00 p.m. on the same day is noon.
If the result appears to have lost its time, change its number format to include time. Choose Home > Number Format and select a date-and-time format, or open Format Cells and select an appropriate custom or time format. A date-only format can hide a real time component, just as it can hide a half-day fraction.
Be careful when the timestamps use different time zones or when local clock changes affect the recorded times. Excel stores the values you enter; it does not automatically interpret time-zone offsets or correct timestamps for daylight-saving changes. Normalize timestamps to a common time basis before calculating a meaningful elapsed-time midpoint.
Common mistakes and useful checks
- Using the wrong dates: Confirm that both endpoints belong to the same interval and that the intended start and end are included.
- Confusing formatting with rounding: A date-only display can conceal a fractional day. Use an explicit rounding formula if a whole date is required.
- Entering dates as text: If the formula returns an error or behaves oddly, check whether the cells contain numeric dates rather than text strings.
- Assuming start must be earlier: AVERAGE returns the same midpoint whichever endpoint is in A2 or B2. Other interval formulas may need the dates in chronological order.
- Ignoring the definition of midpoint: The elapsed-time midpoint and the central item in an inclusive date list can differ. State the rule in the worksheet if other people will use it.
One more check is to compare the distances on each side of the result. For a whole-day midpoint, the number of days from the first endpoint to the result should equal the number from the result to the second. A midpoint containing noon can be correct even when the displayed date alone makes the two sides look unequal.
Frequently Asked Questions
What is the Excel formula for the date halfway between two dates?
With the two dates in A2 and B2, enter =AVERAGE(A2,B2) in another cell and format the result as a date. It returns the exact midpoint, including a fractional day if the endpoints are an odd number of days apart.
Can I calculate the middle date if the dates are in reverse order?
Yes. AVERAGE does not require the earlier date to come first. It returns the same midpoint when the two date values are swapped.
Why does my midpoint show a number instead of a date?
Excel stores dates as numbers. The calculation can be correct while the result cell uses General or Number formatting. Select a date format for the result cell to display it as a calendar date.
How do I return a whole date instead of a time in the middle?
Choose a rounding policy. With the dates in A2 and B2, =ROUND(AVERAGE(A2,B2),0) rounds to the nearest whole day. For a half-day result, Excel rounds forward; use a different explicit rule if your work requires the earlier date.
Does the formula count both endpoint dates?
No special inclusive counting is performed: AVERAGE finds the point halfway between the two date values. If you need the central date in an inclusive list, first decide what to do when the list contains an even number of dates, because there are then two central positions rather than one.
Related
- Excel Named Range Scope Greyed Out? Causes and Fixes
- Excel Named Range Rules: Names, Scope, and Limits
- Excel Named Range Scope: Workbook vs. Worksheet Names
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Ungroup Columns Shortcut: Keys, Steps, and FixesIn Excel for Microsoft 365 on Windows, select the grouped columns and press Alt+Shift+Left Arrow to ungroup them.…
- How to Calculate Age in Excel from Date of BirthCalculate age in Excel with DATEDIF, handle blank or invalid dates, and format results correctly. Follow the steps…
- Excel Freezing Up and Not Responding? What to Do NowIf Excel is freezing or showing “Not Responding,” first wait briefly if it may be calculating or loading…
- Google Sheets Checkbox and Text in One Cell? OptionsNo—a Google Sheets cell cannot display its native, clickable checkbox and separate label text at the same time.…
- How to Group Dates in Excel by Month, Quarter, or YearTo group dates in Excel, create a PivotTable, place the date field in Rows, then right-click a date…
- How to Add Footnotes in Microsoft Word, Step by StepTo add a footnote in Microsoft Word, place the cursor where the reference belongs, then select References >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.