# Convert number of months owned into a date in the past

#### TestingTesting

##### New Member
After searching, I just cant find what I am looking for I am hoping that you wonderful lot can help me out.

I need to convert the field "months owned" into the correct date in the past. However this can not be worked out from the system date, and as this date changes every time a test is run I would like to use an input box to dictate the date to start counting back from. I would also like to output this information into a separate sheet, and preserve the original data.

E.g.: Months owned: "24", Testing date from input box: "01/01/2017", purchase date output: "01/01/2015 00:00" (must be in this format)

Any clues? Even a starting point would be great. I would upload my workbook, but it is a beast! Any questions let me know, any help VERY much appreciated

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### Gerald Higgins

##### Well-known Member
Hi, welcome to the board.

This will be easy to do if you ALWAYS want to calculate a whole number of years back from the test date.
But I'm guessing you probably don't.
So what happens if the months owned = 7 ? Or 7.5 ?
Are you always dealing with the first date in the month ? For both the testing date and the purchase date ?
Do we need to worry about weekends ?

#### Dave Patton

##### Well-known Member
=date(year(a1),month(a1)-24,day(a1))

#### TestingTesting

##### New Member
Thanks for coming back to me. It will always be whole months, first of the month for both dates is just fine.

Weekends, not sure how this relates, but I will only be testing during weekdays, and only need to calculate the month and year in reality

#### AliGW

##### Banned
Possibly this:

Excel 2016 (Windows) 32 bit
EFG
11Months
122420/01/201720/01/2015 00:00

</tbody>
Sheet5

Worksheet Formulas
CellFormula
G12=EDATE(F12,-E12)

</tbody>

<tbody>
</tbody>

Custom formatting for G12 is dd/mm/yyyy hh:mm

#### TestingTesting

##### New Member
AliGW - I think that's done it... I've just given it a quick go, and it seems to be doing the trick, I'll convert it into some VB jiggery pokery and finally I can stop bashing my head against this desk!

#### AliGW

##### Banned
Glad to have helped! I fond the EDATE function very handy.

Replies
2
Views
387
Replies
4
Views
282
Replies
1
Views
245
Replies
10
Views
3K
Replies
2
Views
532

1,190,857
Messages
5,983,252
Members
439,833
Latest member
CDaviess

### 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.

### Which adblocker are you using?

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

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