Excel Macro: Ask user for date then subtract 6 months from it

**foxy_420** · 03-21-2013, 10:36 AM

Hello,

I am trying to create a macro that will ask a user to enter a date (format is: mm/dd/yyyy)

I then want to create a variable that will deduct 6 months from the date entered. I think I am close but not quite there. I can get the 6 months from the CURRENT date, but not the date entered by the user.

Any help would be greatly appreciated

Dim DateAsk As Date
Dim DateWithin6Months As Date


DateAsk = InputBox("Enter the Date the document was extracted in (MM-DD-YYYY) Format")



    DateWithin6Months = DateAdd("m", -6, Date)
    DateWithin6Months = Format(DateWithin6Months, "mm/dd/yyyy")

**stnkynts** · 03-21-2013, 10:51 AM

Try:

Sub Date_Macro()
Dim DateAsk As String
Dim DateWithin6Months As Date


DateAsk = InputBox("Enter the Date the document was extracted in (MM-DD-YYYY) Format")

If Not IsDate(DateAsk) Then
    DateAsk = InputBox("Re-Enter: Must be in date format (MM-DD-YYYY)")
End If

    DateWithin6Months = DateAdd("m", -6, DateAsk)
    DateWithin6Months = Format(DateWithin6Months, "mm/dd/yyyy")

MsgBox DateWithin6Months 'this can be removed.  Here for testing purposes

End Sub

- Changed DateAsk to string to allow for error check
- added in error check
- edited DateWithin6Months to reference DateAsk not Date

**mnjofra** · 03-21-2013, 10:52 AM

Hi,
possibly have to try with DateAsk instead of Date in this sentence:
DateWithin6Months = DateAdd("m", -6, DateAsk)

Regards,

**foxy_420** · 03-21-2013, 11:09 AM

Thanks guys, this works perfect. Mnjofra, your answer was very simple to fix and worked great. I also do very much appreciate your reply to stnkynts. I'll definitely add to both your feedback