VBA Help! Displaying date differences

[sliollio]

Trying to write a macro for an assignment that will let you enter a date, display the year, month, and day in seperate msg boxes, then display if the date entered is from the future/past and compute the difference based on the current date.

Not sure how to get it to calculate the difference between the dates or the future/past part. I tried and If, then, else statement but it would only say "past" regardless of what date was put in.

Any help is greatly appreciated!

Sub macro1()
Dim TheDate As Variant
TheDate = InputBox("Insert: Date (mm/dd/yyyy)")
MsgBox Format(TheDate, "mmmm")
MsgBox Format(TheDate, "dd")
MsgBox Format(TheDate, "yyyy")

End Sub

 

[Momentman]

Just an idea, you can modify further

Sub macro1()
    Dim TheDate As Date
    TheDate = InputBox("Insert: Date (mm/dd/yyyy)")
    Select Case TheDate - Date
        Case Is < 0
            MsgBox "Date is in the past"
        Case 0
            MsgBox "Date is the current date"
        Case Is > 0
            MsgBox "Date is in the future"
    End Select
    MsgBox Format(TheDate, "mmmm")
    MsgBox Format(TheDate, "dd")
    MsgBox Format(TheDate, "yyyy")
End Sub

 

[Michael M]

Geez a man's gotta sleep....can you please stick to the one thread

Duplicate post


http://www.mrexcel.com/forum/excel-questions/804124-need-help-writing-macro.html

 

[sliollio]

Thank you so much! Works perfectly! Do you know if there is a way that within that same MsgBox that displays "past/present/future" that you can display the calculated difference in days? I tried doing some DateDiff functions but can't get it to run. Much less within that same MsgBox

 

[sliollio]

Sorry about that! Was hoping to get some more answers regarding the 3rd part.

 

[Michael M]

Have a look at this previous thread....especially the link to Chip pearsons site !!

http://www.mrexcel.com/forum/excel-questions/33213-calculating-age-visual-basic-applications.html

 

[Momentman]

Note that i used days all through, but if the difference is just 1 day, that should be singular, you will need an if statement for that or add more cases

Sub macro1()
    Dim TheDate As Date
    TheDate = InputBox("Insert: Date (mm/dd/yyyy)")
    Select Case TheDate - Date
        Case Is < 0
            MsgBox "Date is in the past by " & Date - TheDate & "days"
        Case 0
            MsgBox "Date is the current date"
        Case Is > 0
            MsgBox "Date is in the future " & TheDate - Date & "days"
    End Select
    MsgBox Format(TheDate, "mmmm")
    MsgBox Format(TheDate, "dd")
    MsgBox Format(TheDate, "yyyy")
End Sub