Compicated Formulas in a MsgBox

R

rocksolid77

New Member
Joined
Sep 19, 2011
Messages
18
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),>
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Would this work?
Code: 
MsgBox Format(Range("A2").Value - Range("A1").Value, "yy ""years"" m "" months"" d ""days""")
 
Upvote 0
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!
 
Upvote 0
I get the impression i'm almost there.

I've adapted the above code to the following

Code: 
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
 
Upvote 0
ok, so I realized that change DateX one to Date_X fixed the mismatch but now i'm getting the same error at

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

which seemed to be working earlier
 
Upvote 0
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:

Rich (BB code): 
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
 
Upvote 0
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
 
Upvote 0
Try the code below

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
534
Legacy 352679
L
J
  • Solved
Determine total numbers of days worked in a given month/year from a table of date ranges
Replies
3
Views
157
Eric W
Eric W
A
  • Question
Using IF,AND, with DATEDIF
Replies
2
Views
382
Alex Blakenburg
A
K
  • Solved
Prorating Salary based on hire date and Term date - by month
Replies
4
Views
297
KH Consulting
K
D
  • Solved
something wrong in the form of Interactive calendar
Replies
3
Views
132
dmt32
D

Forum statistics

Threads
1,216,725
Messages
6,132,346
Members
449,719
Latest member
excel4mac

Share this page

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