# sum date format with number format

#### xymcrush17

##### New Member
hi mrExcel
I have data like this

1112 which mean (NOV-2012) and sum 18 which mean (1year 6 months) result I want is 05-2014 which mean ( May 2014)

thanks for advance

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Special-K99

##### Well-known Member
=DATE(RIGHT(A1,2),LEFT(A1,2)+18,1)

where A1 is the date

#### xymcrush17

##### New Member
the result is 5/1/1914

not 2014

Try:

Excel Workbook
ABC
111121805-2014
Sheet1

#### xymcrush17

##### New Member
i have tried the result of your formula is 07-2013

#### FormR

##### MrExcel MVP
This assumes all your dates are in the 21st century.

Does this work for you?

=TEXT(DATE("20"&RIGHT(A1,2),LEFT(A1,2)+B1,1),"mm-yyyy")

Where B1 contains 18

#### Special-K99

##### Well-known Member
I'm sure there's a shorter way

=DATE(YEAR(DATEVALUE("01/"&LEFT(A1,2)&"/"&RIGHT(A1,2))+0),LEFT(A1,2)+18,1)

#### xymcrush17

##### New Member
i just tricked with your formula
thanks you add with 20 be 112012

the result i got is May-14

#### FormR

##### MrExcel MVP
i just tricked with your formula
thanks you add with 20 be 112012

the result i got is May-14

Sorry, I don't follow?

#### Jborg

##### Board Regular
Try this: Do not forget the cell ref:

=DATE(YEAR("1january"&RIGHT(B5,2)),LEFT(B5,2)+18,5)

Replies
3
Views
276
Replies
3
Views
202
Replies
3
Views
306
Replies
11
Views
958
Replies
0
Views
196

Threads
1,191,686
Messages
5,988,066
Members
440,125
Latest member
vincentchu2369

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back