Excel Midpoint of Dates: Formula and Examples

Formulas & functions · Updated November 19, 2026

To find the midpoint between two dates in Excel, put the earlier date in A2 and the later date in B2, then enter =A2+(B2-A2)/2 in another cell. Excel averages the underlying date values, so the result can include a time as well as a date.

The basic midpoint formula

Excel stores dates as serial numbers: each calendar date corresponds to a number, and a time is represented by a decimal fraction of a day. That makes a date midpoint an ordinary arithmetic calculation. Subtract the start from the end to get the elapsed time, divide by two, and add half the interval to the start.

With the start date in A2 and the end date in B2, the formula is =A2+(B2-A2)/2. It works whether A2 is earlier or later than B2: the result is the point halfway between the two values. The cells must contain dates Excel recognizes as dates, rather than text that only looks like a date.

An equivalent option is the AVERAGE function. With the two dates in A2 and B2, enter =AVERAGE(A2,B2). Both formulas return the same midpoint for two numeric date values. The first formula makes the elapsed-time logic explicit; AVERAGE can be convenient when you are already working with a range or list.

A worked example

Suppose A2 contains January 1, 2025, and B2 contains January 10, 2025. The elapsed interval is nine days. Half of nine is 4.5, so the midpoint falls four and a half days after January 1: January 5 at noon. With the dates in A2 and B2, =A2+(B2-A2)/2 returns that date-and-time value.

If the result cell is formatted to show only a date, Excel hides the time portion. It may therefore display January 5 even though the exact midpoint is noon on that date. To see the time, select the result cell and choose Home > Number Format dropdown, then select a date-and-time format if one is available. You can also press Ctrl+1 on Windows (Command+1 on Mac) to open Format Cells and choose a suitable date or custom format.

A second example shows why some midpoints land at midnight. With April 1, 2025 in A2 and April 11, 2025 in B2, the difference is ten days, so the midpoint is five days after the start: April 6, 2025. The result is a whole-day serial value, with no time fraction to display.

What midpoint do you mean?

For most date calculations, midpoint means halfway through the elapsed time between two date-time values. The formula uses the actual difference between the endpoints, not the number of calendar dates someone might count inclusively. This distinction matters when an interval contains an odd number of days.

For example, January 1 through January 10 contains ten named calendar dates if both endpoints are included in a list. But the elapsed time from the start of January 1 to the start of January 10 is nine days. Its exact midpoint is January 5 at noon, between the start of January 5 and the start of January 6. If you are selecting a central date from an inclusive list of dates, there are two middle dates in a ten-item list; you may need to choose one according to your reporting rule.

Also decide whether times matter. If the cells contain dates with times, Excel includes those times in the calculation. A start of 9:00 a.m. and an end of 5:00 p.m. on the same date have a midpoint of 1:00 p.m. If you intend to compare calendar days only, remove or ignore the time component deliberately rather than assuming the cells contain midnight values.

Show a date only or round to a calendar day

Changing a number format changes what Excel displays, not the value used in later calculations. A date-only format can make a half-day midpoint look like an ordinary date, while the hidden time remains in the cell. That is usually fine for a timeline or a calculation that needs the precise halfway instant.

If you specifically need the nearest whole date, round the result. With the original dates in A2 and B2, enter =ROUND(A2+(B2-A2)/2,0). For the January 1 and January 10 example, the exact midpoint is January 5 at noon, and this formula returns January 6 because Excel rounds a positive half to the next integer. This is a rounding choice, not a different exact midpoint.

If your rule is to use the earlier calendar date whenever the midpoint is between dates, use a date-only display or apply a downward rounding rule that matches your data. Be careful about substituting INT without considering the date range: INT rounds down to the next lower integer, and it is not a neutral formatting operation. In reports, document whether you are showing an exact midpoint, rounding to the nearest day, or choosing one of two central dates.

Calculate midpoints for many rows

For a table of start and end dates, put the start dates in column A and the end dates in column B. In C2, enter =A2+(B2-A2)/2, then fill the formula down the column using the fill handle or copy and paste. Excel adjusts the row references automatically, so the next row calculates from A3 and B3.

For a larger dataset, format the range as an Excel table with Insert > Table. A formula entered in the midpoint column is generally filled through the table as a calculated column, and new table rows can inherit the formula. Check that the displayed result format is suitable: a copied formula can calculate correctly while still showing an unhelpful number format.

If you have a list of several dates and want the average date, that is a different question from finding the midpoint of the earliest and latest dates. With dates in A2:A10, =AVERAGE(A2:A10) calculates their arithmetic mean, ignoring blank cells. To get the midpoint of the range endpoints, first identify the minimum and maximum dates; averaging every date in the list can produce a different answer, especially if dates are unevenly distributed.

Check that Excel recognizes both dates

A cell can display something date-like while containing text. If the midpoint formula gives a #VALUE! error, or if subtraction behaves unexpectedly, inspect the input cells. Select each one and look at the formula bar and number format. A genuine Excel date is stored as a number; text imported from another system may need to be converted before date arithmetic works.

  • Use a consistent, unambiguous date entry style, such as dates selected with the date picker where available or entered in a format Excel correctly interprets under your regional settings.
  • If dates came from a text file, use Data > Text to Columns to review and convert the date column, choosing the correct date order when prompted.
  • Check for leading or trailing spaces and mixed formats, such as some values stored as dates and others stored as text.
  • Confirm that neither endpoint is blank. A blank can be treated like zero in some arithmetic contexts and produce a misleading result.

Date interpretation can depend on regional settings. For instance, a text value with a day and month both at or below 12 may be ambiguous between month/day and day/month conventions. Converting it under the wrong convention can create a valid but incorrect date, so verify a few converted values against the original source.

Common errors and edge cases

If the midpoint appears as a number such as 45662, the calculation may be correct: the result cell is simply formatted as General or Number. Select it and choose a date format from Home > Number Format dropdown. If it shows a date but you expected a time, use a format that includes hours and minutes.

Reversed endpoints are not a problem for the midpoint formula because averaging two values is independent of their order. However, if your workbook also calculates duration with a subtraction formula, a reversed pair will produce a negative duration. You may want to validate that the start date is no later than the end date when negative intervals are not allowed.

Dates with times are averaged as complete date-time values. A midpoint can therefore fall on a different clock time from either endpoint. Excel does not account for time zones or daylight-saving rules when you average serial values; it performs arithmetic on the stored numbers. If the dates represent timestamps from different time zones, normalize them to a common time basis before calculating an elapsed midpoint.

Excel workbooks can use different date systems, most commonly the 1900 and 1904 systems. Within a workbook, dates entered and calculated consistently use that workbook’s system, so the midpoint formulas above work normally. Caution is needed when transferring raw date serial numbers between workbooks configured with different systems; use actual date values and verify the displayed dates after copying or importing.

Frequently Asked Questions

What is the formula for the midpoint between two dates in Excel?
With the start date in A2 and the end date in B2, use =A2+(B2-A2)/2. It calculates the exact halfway point, including a time fraction when the interval has an odd number of days.

Can I use AVERAGE to find the midpoint of two dates?
Yes. With the two Excel date values in A2 and B2, =AVERAGE(A2,B2) returns the same midpoint as the elapsed-time formula. Both inputs need to be numeric dates, not unconverted text.

Why does my midpoint show a number instead of a date?
Excel stores dates as serial numbers, and the result cell may be formatted as General or Number. Apply a date format from Home > Number Format dropdown; if you need the precise halfway time too, choose a date-and-time format.

How do I get the midpoint as a whole date?
With the two dates in A2 and B2, use =ROUND(A2+(B2-A2)/2,0) to round the exact midpoint to the nearest whole date serial. Decide how your process should handle a midpoint exactly at noon, since rounding can select the following calendar date.

Does the formula work when the dates are in reverse order?
Yes. Averaging two date values gives the same midpoint whichever endpoint comes first. For separate duration calculations, though, reverse order produces a negative difference, so validate the order if negative durations are not meaningful.

Can Excel find the midpoint of dates that include times?
Yes. Excel includes the time fractions stored in the cells, so the answer can fall between clock times or at a particular time of day. Use a date-and-time number format to display that part of the result.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.