Compicated Formulas in a MsgBox

[rocksolid77]

Hi All, Any help will be greatly appreciated. Basically what I'm trying to do is to create a macro that can tell us how much time has passed between two dates. This can be accomplished with the following formula:

=YEAR(A2)-YEAR(A1)-IF(OR(MONTH(A2)<month(a1),and(month(a2)=month(a1), day(a2)<day(a1))),1,0)&"="" years,="" "&month(a2)-month(a1)+if(and(month(a2)="" <="MONTH(A1),DAY(A2)<DAY(A1)),11,IF(AND(MONTH(A2)<MONTH(A1),DAY(A2)">=DAY(A1)),12,IF(AND(MONTH(A2)>MONTH(A1),DAY(A2)<day(a1)),-1)))&" months,="" "&a2-date(year(a2),month(a2)-if(day(a2)<day(a1),1,0),day(a1))&"="" days"<="" pre=""> Where A2 is the most recent date
and A1 is the oldest date
and will return the answer in the following format x Years, X Months, X Days

What I'm trying to do is have this as a macro with input boxes and a MsgBox rather than have the actual calculations being done in the worksheet. Unfortunately I'm still very noob with VBA and am not sure how to go about this, or if it's even possible.

I assume I can just have the value of the input boxes added to cells on the spreadsheet, calculate it in the sheet and return the value of the cell as a message box but was hoping there was some way to do this without affecting the contents of the sheet.

There is an 8 year old thread on this but i thought it would be better to start a new one. http://www.mrexcel.com/forum/showthread.php?t=83164

Also, if there's a way to do this with cell selections instead of input boxes that would be awesome.
Thanks in advance for any help!
</day(a1)),-1)))&"></month(a1),and(month(a2)=month(a1),>

 

[mikerickson]

Would this work?

MsgBox Format(Range("A2").Value - Range("A1").Value, "yy ""years"" m "" months"" d ""days""")

 

[rocksolid77]

Thanks for you time. Yes that works perfectly when the values are entered into A1 and A2 but what I was wondering is if there was a way to do this with the dates entered into Input Boxes rather then in cells.

Or (Maybe a Pipe Dream) based on two selected cells as in, actively selected not predetermined cells.

In the mean time I will gladly use your code and thank you good sir!

 

[rocksolid77]

I get the impression i'm almost there.

I've adapted the above code to the following

Sub TimeBetween()

Dim Date1 As Integer 'Variable for the the oldest date in your calculation
Dim Date2 As Integer 'Variable for the most recent date in your calculation

'Ask for input data for the oldest date.
Date1 = InputBox("Please, indicate the oldest date in your calculations with the format MM-DD-YYYY", "Date 1", "1-26-2011")

'Ask for input data for the most recent date.
Date2 = InputBox("Please, indicate the most recent date in your calculations with the format MM-DD-YYYY", "Date 2", "1-26-2011")

'Output the number of Years, Months, Days between both dates.
MsgBox Format(Date2 - Date1, "yy ""years"" m "" months"" d ""days""")

But I get

"Run-Time error '13': Type Mismatch"

With

Date1 = InputBox("Please, indicate the most recent date in your calculations with the format MM-DD-YYYY", "Date 2", "1-26-2011")
Highlighted

 

[rocksolid77]

ok, so I realized that change DateX one to Date_X fixed the mismatch but now i'm getting the same error at

MsgBox Format(Date_2 - Date_1, "yy ""years"" m "" months"" d ""days""")

which seemed to be working earlier

 

[GTO]

Hi there,

See vba help, but InputBox returns a String. Thus - if Date1 is declared as Integer, Long, etc, it falls down. You can coerce the string to a Date, something like:

Option Explicit
    
Sub test()
Dim i As Long
Dim ary(0 To 1)
    
    For i = 0 To 1
        ary(i) = InputBox(Array("Enter oldest date", "Enter newest date")(i), "Date Difference", Format(Evaluate("=TODAY()"), "mm/dd/yyyy"))
    Next
    
    If IsDate(ary(0)) And IsDate(ary(1)) Then
        'MsgBox CLng(CDate(ary(1))) - CLng(CDate(ary(0)))
        MsgBox Format(CDate(ary(1)) - CDate(ary(0)), "yy ""years"" m "" months"" d ""days""")
    Else
        MsgBox "One or both entries would not evaluate to a Date"
    End If
End Sub

 

[rocksolid77]

Hi GTO,

Thanks for your Reply. I don't quite understand the code but it seems to be doing what I'm asking for. The only problem is it seems to be returning incorrect answers, example:

09/19/2011 & 09/20/2011 return 99 Years, 12 Months, 31 Days
09/19/2011 & 09/25/2011 returns 1 Month 5 Days

 

[Biz]

Try the code below

Sub test()
Dim i As Long, a
Dim ary(0 To 1)
    For i = 0 To 1
        ary(i) = InputBox(Array("Enter oldest date", "Enter newest date")(i), "Date Difference", Format(Evaluate("=TODAY()"), "mm/dd/yyyy"))
    Next
    If IsDate(ary(0)) And IsDate(ary(1)) Then
       MsgBox Age(CDate(ary(0)), CDate(ary(1)))
    Else
        MsgBox "One or both entries would not evaluate to a Date"
    End If
End Sub
Function Age(Date1 As Date, Date2 As Date) As String
    Dim Y As Integer
    Dim M As Integer
    Dim D As Integer
    Dim Temp1 As Date
    Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1))
    Y = Year(Date2) - Year(Date1) + (Temp1 > Date2)
    M = Month(Date2) - Month(Date1) - (12 * (Temp1 > Date2))
    D = Day(Date2) - Day(Date1)
    If D < 0 Then
        M = M - 1
        D = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + D + 1
    End If
    Age = Y & " years " & M & " months " & D & " days"
End Function

Does it help?

Biz

 

[rocksolid77]

Yes! That's perfect!

Thank you all for your help!

 

[GTO]

@ Biz:

That's nice!