Convert number of months owned into a date in the past

TestingTesting

New Member
Joined
Dec 20, 2016
Messages
3
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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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 ?
 
Upvote 0
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
 
Upvote 0
Possibly this:

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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet5

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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Custom formatting for G12 is dd/mm/yyyy hh:mm
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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

Which adblocker are you using?

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
Back
Top