Function Problems

Add Whole or Decimal Months to a Date in Excel

Jason MillerJason Miller Nov 18, 2026 868 views

Question details

Add a whole or decimal number of months to a date, with an option to return a month-end date.

Add Whole or Decimal Months to a Date in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A spreadsheet contains a start date and a month offset that may include a fractional month.
Observed behavior
The desired result is either a date adjusted by the fractional month or a date aligned to month end.
Before you start

Enter a valid Excel date in F6 and a numeric month offset in E8. The decimal-month formulas below assume E8 is zero or positive; format the result cell as a date.

Solution 1Recommended

Calculate a date using a fractional month

Use this method when the decimal portion should add a proportional number of days rather than round to a whole month.

The formula adds the whole months first, then calculates the fractional portion from the number of days between that result and the next whole-month result. It rounds the added days up.

This is one way to define a fractional month; other conventions may produce different dates.

1
Enter the date and offset

Enter the start date in F6 and a nonnegative number such as 1.5 in E8.

2
Enter the formula

Select the result cell and enter =EDATE(F6,E8)+ROUNDUP((EDATE(F6,E8+1)-EDATE(F6,E8))*MOD(E8,1),0).

3
Format the result

Select the result cell, then choose Home > Number Format > Short Date or another date format.

Calculate a date using a fractional month
Calculate dates in WPS Spreadsheets

Add fractional months to dates in WPS Spreadsheets

Use EDATE and ROUNDUP in WPS Spreadsheets to calculate a date from a whole or fractional month offset, then format the result as a date.

  1. 1. Open a spreadsheet: Open WPS Spreadsheets and create a workbook or open an existing Excel file.
  2. 2. Enter the inputs: Enter a start date in F6 and a nonnegative month offset, such as 1.5, in E8.
  3. 3. Calculate the result: Select an empty cell and enter =EDATE(F6,E8)+ROUNDUP((EDATE(F6,E8+1)-EDATE(F6,E8))*MOD(E8,1),0).
  4. 4. Apply a date format: Select the result cell and use the Home tab's number format controls to display it as a date.
Calculate fractional month offsets with a spreadsheet formulaOpen and edit Microsoft Excel XLS and XLSX filesUse familiar spreadsheet cells and formula controlsWork with a free, easy-to-use office suite
microsoft office alternative - wps office

Frequently Asked Questions

Does EDATE add the decimal part of a month?

No. EDATE truncates the month argument to an integer. To include a fractional portion, calculate proportional days separately and add them to the EDATE result.

How do I keep the result at the end of a month?

For a nonnegative offset in E8 and a date in F6, enter =EOMONTH(F6,INT(E8)+ROUNDUP(MOD(E8,1),0)). A positive fractional offset is rounded up to the next whole month.

Why does my formula show a number instead of a date?

Excel stores dates as serial numbers. Select the result cell and choose Home > Number Format > Short Date or another date format.

Can I use these fractional-month formulas with negative offsets?

The fractional-month formulas on this page assume nonnegative offsets. For a whole-number offset, use =EDATE(F6,E8) with a negative value in E8 to move backward.