Convert year-month intervals to months

Turn an interval like +02-03 into 27 calendar months, not days.

What you'll make

In Excel: +02-03 becomes 27 months.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1IF(LEFT(A8,1)="-",-1,1)Gives -1 if the interval starts with a minus, else 1.1
2VALUE(MID(A8,2,LEN(A8)-4))*12Reads the years after the sign and turns them into months.24
3VALUE(RIGHT(A8,2))Adds the last 2 digits as months.27

Part 1 sets the sign. Parts 2 and 3 add up the months, then the sign is applied to the total.

=IF(LEFT(A8,1)="-",-1,1)*(VALUE(MID(A8,2,LEN(A8)-4))*12+VALUE(RIGHT(A8,2)))

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own intervals. Paste intervals like +02-03 into the green column of the Template tab. The totals fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

These are calendar months, not days. To get an end date, add them to a start date with EDATE.

Microsoft's help: VALUE function