Can this formula be put in a message box?

[tim 105]

Just find this site, lots of useful info here!

I can enter this MsgBox 5 + 5 and get 10 when I call the message box which works fine, but if I try this MsgBox DATEDIF(TODAY(), 40696, "y") & " years " & DATEDIF(TODAY(), 40696, "ym") & " months " & DATEDIF(TODAY(), 40696, "md") & " days"
I get compile error sub or function not defined, so can a formula like this be put in a message box? I can reference the cell with the formula like this, MsgBox Range("A1"), and it works but I would like to have it all in the message box if possible, Thanks Tim

 

[Seti]

A quick check of VBA help shows that DATDIF is not supported in VBA, even with application.worksheetfunction. There may be some way to get it to work but others will have to chime in as it is over my head. VBA offers the DATEDIFF (note the 2 F's) function but it is not going to be as clean as you want. You can check it out in the help system for VBA.

 

[jimboy]

Hi - welcome to the board.

Not sure what you are trying to do...

...is this what you are after?

MsgBox [A1].Formula

 

[Greg Truby]

I was going to mention what Seti did DateDiff(). Also, VBA has Date; it does not have Today() unless you access it via the collection of WS functions [application.worksheetfunctions.today()] The rest is just working out the math...

 

[tim 105]

Ok, so after looking at DateDiff I can use this,
MsgBox DateDiff("yyyy", Date, 40696) & " Years ", to get years, or months or days by changing yyyy to m or d , but being new to this VBA stuff I have no idea how, or if it can be done, to get it to show years, months and days like the formula in the cell shows, should be 7 years 1 months 7 days. Tried MsgBox DateDiff("yyyy", Now, 40696) & " Years " & DateDiff("m", Now, 40696) & " Months " & DateDiff("d", Now, 40696) & " Days ", but this gives 7 years 86 months 2593 days, if this can not be done I will just continue to reference the cell address. Thanks Tim