To find the midpoint of two dates in Excel, enter the start date in A2 and the end date in B2, then use =AVERAGE(A2,B2). Excel averages the underlying date values; format the result as a date, or include a time format if the midpoint falls between calendar days.
The quick formula and what it returns
With the earlier date in A2 and the later date in B2, this formula returns the exact point halfway between them:
=AVERAGE(A2,B2)
For example, if A2 contains January 1, 2025, and B2 contains January 11, 2025, the elapsed interval is 10 days. The midpoint is January 6, 2025. If the dates are January 1 and January 10, the interval is 9 days, so the exact midpoint is January 5 at noon. A cell formatted to show dates only will display January 5 and hide the time portion.
The formula works with dates in either order. AVERAGE adds the two date values and divides by two, so swapping the start and end cells does not change the result. You do not need a special midpoint function: Excel has no dedicated worksheet function named for finding the midpoint of two dates.
Why averaging dates works
Excel stores ordinary dates as serial numbers: each calendar day corresponds to a number, and time is represented as a fraction of a day. That means the date arithmetic is numerical even though cells are usually displayed in a readable date format. Averaging the two numbers places the result equally far from each endpoint.
This is the elapsed-time midpoint, not a count of the dates inclusively. From January 1 through January 10 there are 10 named calendar dates if both endpoints are counted, but only 9 days have elapsed between midnight at the start of January 1 and midnight at the start of January 10. The mathematical midpoint is therefore 4.5 days after January 1: January 5 at noon. This distinction explains why a midpoint can include a time even when both inputs look like whole dates.
The calculation assumes both inputs are valid Excel date values and belong to the same date system. For normal dates entered in the same workbook, that is generally the case. Results can be misleading if one entry is actually text or if values copied from another workbook use incompatible date-system assumptions.
Enter the dates and calculate the midpoint
For a simple worksheet, use two labeled input cells and a result cell. In Excel for Microsoft 365 on Windows, these steps are sufficient:
- Type a label such as Start date in A1 and End date in B1.
- Enter the first date in A2 and the second date in B2. Excel commonly recognizes entries such as 1/1/2025 as dates, though the accepted order depends on your regional settings.
- Click C2, the cell where you want the answer, and enter the midpoint formula. With the dates in A2 and B2, use
=AVERAGE(A2,B2). - Press Enter. If the result appears as a number rather than a date, apply a date number format to C2.
In that example, if A2 is January 1, 2025, and B2 is January 11, 2025, the formula returns January 6, 2025. If you have many rows, keep the same arrangement for each row and fill the result formula down. For example, row 3 would refer to A3 and B3 rather than the row 2 inputs.
The order of the dates is not important for the average, but clear labels still help prevent data-entry mistakes. If the cells represent different meanings, such as a project start and a target finish, keeping those meanings visible makes it easier to interpret the midpoint and catch an accidentally incorrect date.
When the midpoint falls between whole dates
An exact midpoint does not always land at midnight. If the number of elapsed days between two date-only values is odd, half of that interval is a half-day. For January 1 and January 10, the midpoint is January 5 at 12:00 PM. The exact average preserves that half day; it does not arbitrarily choose one of the two neighboring dates.
Choose how to handle that fraction based on what the answer is for. If you need the precise instant, retain the time and display it. If your task requires a calendar date, decide whether to round to the nearest date or consistently use the earlier date. With the same inputs in A2 and B2, the following formula rounds the midpoint to the nearest whole date:
=ROUND(AVERAGE(A2,B2),0)
For January 1 and January 10, this returns January 6 because the exact midpoint is January 5.5 and Excel rounds that positive value up to the next whole day. If instead you need to discard the time and keep the date at or before the exact midpoint, use this formula with the same A2 and B2 layout:
=INT(AVERAGE(A2,B2))
That result is January 5 for the same example. Rounding is a choice about how to report the answer, not a different calculation of the exact midpoint. State the rule in your worksheet or documentation when other people will use the result.
Include times when the interval is measured in hours
If A2 and B2 contain date-and-time values, averaging them finds the halfway instant. For example, a meeting scheduled from 9:00 AM to 11:00 AM on the same day has a midpoint of 10:00 AM. The ordinary formula works because Excel stores the time as a fraction of the date’s serial value.
Cell formatting controls what you can see, not the underlying answer. A date-only format can make a result with a time appear to be just a date. To inspect the time, select the result cell and open Format Cells with Ctrl+1 on Windows or Command+1 on Mac. Choose a date-and-time display, or use a custom format such as m/d/yyyy h:mm AM/PM. Excel for the web has number-format controls on the Home tab; the available interface can differ slightly from the desktop dialog.
Check the meaning of the input times before interpreting the result. A value entered as a date without a time is treated as midnight. Thus, averaging a date-only value with a timestamp can produce an answer that is mathematically consistent but not what you intended if the date-only entry was meant to represent the end of that day. Add the appropriate times to the inputs when the real interval requires them.
Format the answer without changing it
Excel may initially display the answer as a serial number, especially if the result cell is formatted as General. That is not evidence that the formula failed. Select the result cell, then use Home > Number Format in the Number group to choose a date style. Choose a date-and-time format if the half-day or time-of-day matters.
Changing a number format changes only the display. It does not round the serial value or remove its fractional part. This is useful when you want a clean date on screen while retaining the exact midpoint for later calculations. Conversely, if downstream calculations must use a whole date, use an explicit rounding formula rather than relying on a date-only display.
Regional settings affect how dates are entered and shown. For example, a numeric date such as 4/5/2025 can be interpreted differently in month-day-year and day-month-year regions. If a result looks wrong, verify that the source cells contain the intended dates by selecting each one and checking the formula bar, rather than assuming the display alone confirms the underlying value.
Common errors and how to diagnose them
- The result is a number. The calculation may be correct, but the result cell is using General or Number formatting. Apply a date format to display it as a calendar date.
- The formula shows an error. Check for misspelled cell references, text in place of a date, or a formula entered without the leading equals sign. A date typed with a format Excel does not recognize for your region may be stored as text.
- The answer seems one day early or late. Check whether you are comparing elapsed days or counting both endpoints. Also check whether the exact midpoint includes noon and the cell’s format is hiding that time.
- A blank input produces an unexpected answer. AVERAGE ignores blank cells in a referenced range, so one entered date and one blank can return the date that is present. Make sure both inputs are filled before treating the result as valid.
- The dates were copied from another workbook. Excel workbooks can use different 1900 and 1904 date systems. If values were transferred between workbooks and dates shift by a large amount, inspect the source and destination date-system settings and verify the actual dates.
For a reliable result, make sure both input cells contain numeric date values, not strings that merely look like dates. You can test by changing the input cell’s format temporarily to General: a valid date will appear as a serial number. Restore the desired date format afterward. Avoid correcting a questionable result by manually typing over the formula; fix the source values or the display rule so recalculation remains dependable.
Alternative formulas and when to use them
AVERAGE is concise and communicates the intent clearly. Another equivalent calculation starts at the first date and adds half the difference. With the earlier or later date in A2 and the other date in B2, this formula finds the same exact midpoint:
=A2+(B2-A2)/2
This version can be useful when explaining the calculation as start plus half the interval. It also works when B2 is earlier than A2: the difference becomes negative, and adding half that difference still lands halfway between the two values. For most worksheets, AVERAGE is simpler to read. Use one approach consistently so someone reviewing the sheet can follow it easily.
A calendar-day midpoint is not necessarily the midpoint of working days. If a delivery period runs from Monday to Friday, the elapsed-time midpoint is Wednesday, but holidays, weekends, shifts, or business-hour schedules can change what a business midpoint means. Define whether you mean half the elapsed clock time, the middle working date, or the middle working hour before building a more specialized calculation. Excel’s ordinary date average does not exclude weekends or holidays.
Likewise, a date midpoint is not the same as the middle item in a list of dates. If you have many dates and want the median date, that is a different statistical question: the median is the central value after sorting the list, while the midpoint here means halfway between two specified endpoints.
Frequently Asked Questions
Does Excel have a MIDPOINT function for dates?
No. Use AVERAGE on the two date cells, or add half their difference. Excel treats dates as numeric serial values, so ordinary arithmetic gives the exact midpoint.
What formula finds the midpoint when the dates are in different cells?
If the dates are in A2 and B2, enter =AVERAGE(A2,B2) in the result cell. Replace those references with the cells that contain your two dates.
Why does the midpoint show a decimal or a time?
A decimal portion represents part of a day. A midpoint ending in .5 is halfway through a day, usually noon when the inputs are whole dates. Apply a date-and-time format to see it.
How do I make the midpoint a whole date?
Choose a rounding rule. ROUND to zero decimal places gives the nearest whole date; INT keeps the whole date at or before the exact midpoint for ordinary positive Excel date serials.
Does the order of the two dates matter?
No. Averaging the same two values gives the same answer in either order. Still, labeling start and end dates clearly helps you verify that the inputs are the dates you intended.
Related
- Microsoft Excel Strikethrough Shortcut and How to Use It
- Excel: How to Shade Every Other Row
- Microsoft Excel Snowball Debt Payoff Template Setup
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Data Validation If-Then Lists: How They WorkYes. In Excel for Microsoft 365 on Windows, an if-then list is usually a dependent dropdown: the choice…
- Using the IFS Function in Excel: Syntax and ExamplesIn Excel, use IFS to test conditions in order and return the result paired with the first condition…
- Excel Formula for the Higher of Two Values: MAX or IFTo return the higher of two values in Excel, use the MAX function. If the values are in…
- Median Formula Examples in Excel and by HandThe median is the middle value after a set of numbers is arranged from smallest to largest; with…
- Excel Formula for a Whole Column: Options and ExamplesFor a calculation that should use every cell in a column, use a whole-column reference such as =SUM(B:B);…
- Excel Filter Dates by Day of Week: Methods That WorkExcel’s date filter menu does not include a direct day-of-week option, but you can filter weekdays by adding…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.