elapsed time in msgbox
 

How can I format the result of somthing like
msgbox(now()-37790.0756944444)
as 0 years, 6 months, 19 days, 11 hours, 5 minutes, 17 seconds?
(the 37790.0756944444 is just an example, but the date will be constant). In a worksheet it's no problem,

"=DATEDIF($A$4,NOW(),""y"")&"" years, ""&DATEDIF($A$4,NOW(),""ym"")&"" months, ""&DATEDIF($A$4,NOW(),""md"")&"" days,"" &TEXT(NOW()-$A$4,""h"") &"" hours, "" &TEXT(NOW()-$A$4,""m"")&"" minutes, "" & TEXT(NOW()-$A$4,""s"") & "" seconds"""

The thing is, I don't need the woksheet, I just want to attach the elapsed time to a button that will calculate when clicked. It twould look cooler if it keeps calculating while it's up, but that part isn't necessary. Thanks in advance for any/all help.

elapsed time in msgbox
 

Rick,

This should do it

MsgBox Format(Now - 37790.0756944444, "yy ""Years"" m ""months"" d
""days"" h ""hours"" mm ""minutes"" ss ""seconds""")


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rick" wrote in message
...
How can I format the result of somthing like
msgbox(now()-37790.0756944444)
as 0 years, 6 months, 19 days, 11 hours, 5 minutes, 17 seconds?
(the 37790.0756944444 is just an example, but the date will be constant).
In a worksheet it's no problem,

"=DATEDIF($A$4,NOW(),""y"")&"" years, ""&DATEDIF($A$4,NOW(),""ym"")&""
months, ""&DATEDIF($A$4,NOW(),""md"")&"" days,"" &TEXT(NOW()-$A$4,""h"") &""
hours, "" &TEXT(NOW()-$A$4,""m"")&"" minutes, "" & TEXT(NOW()-$A$4,""s"") &
"" seconds"""

The thing is, I don't need the woksheet, I just want to attach the elapsed
time to a button that will calculate when clicked. It twould look cooler if
it keeps calculating while it's up, but that part isn't necessary. Thanks
in advance for any/all help.

elapsed time in msgbox
 

The only thing that confuses me now is that in the worksheet, I got "0 years, 6 months, 19 days,
12 hours, 7 minutes, 57 seconds" as a result. Only a few seconds later in the immediate window with the solution above, I got 00 years, 7 months, 20 days, 12 hours, 55 minutes, 48 seconds. The equation is the same in both...

elapsed time in msgbox
 

I forgot to add... Thanks for the syntax. Works like a charm.

elapsed time in msgbox
 

I get 7 months, 20 days etc. No idea how you got 6 months unless your clock
was wrong.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rick" wrote in message
...
The only thing that confuses me now is that in the worksheet, I got "0
years, 6 months, 19 days,
12 hours, 7 minutes, 57 seconds" as a result. Only a few seconds later in
the immediate window with the solution above, I got 00 years, 7 months, 20
days, 12 hours, 55 minutes, 48 seconds. The equation is the same in both...

elapsed time in msgbox
 

If you put the formula into a cell

=DATEDIF($A$4,NOW(),""y"")&"" years, ""&DATEDIF($A$4,NOW(),""ym"")&"" months, ""&DATEDIF($A$4,NOW(),""md"")&"" days,"" &TEXT(NOW()-$A$4,""h"") &"" hours, "" &TEXT(NOW()-$A$4,""m"")&"" minutes, "" & TEXT(NOW()-$A$4,""s"") & "" seconds"""

and the put the dateserial into A4 "37790.0756944444"

you get a different answer than the seemingly same formula in the immediate window. Also, if you cahnge the formatting, ie. yyy instead of y or mmm instead of mm, the result changes significantly. Jul Months, etc. Could it be how vb interprets NOW? I've tried TODAY & DATE, all of which produce different results than in a worksheet formula. I know I'm new at this, but I'm really confused at how the math is done.

elapsed time in msgbox
 

Aah yes! That is because DATEDIF works out all of the differences in months
and days etc. without regard to the true date differences.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rick" wrote in message
...
If you put the formula into a cell

=DATEDIF($A$4,NOW(),""y"")&"" years, ""&DATEDIF($A$4,NOW(),""ym"")&""
months, ""&DATEDIF($A$4,NOW(),""md"")&"" days,"" &TEXT(NOW()-$A$4,""h"") &""
hours, "" &TEXT(NOW()-$A$4,""m"")&"" minutes, "" & TEXT(NOW()-$A$4,""s"") &
"" seconds"""

and the put the dateserial into A4 "37790.0756944444"

you get a different answer than the seemingly same formula in the
immediate window. Also, if you cahnge the formatting, ie. yyy instead of y
or mmm instead of mm, the result changes significantly. Jul Months, etc.
Could it be how vb interprets NOW? I've tried TODAY & DATE, all of which
produce different results than in a worksheet formula. I know I'm new at
this, but I'm really confused at how the math is done.

elapsed time in msgbox
 

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rick" wrote in message
...
I forgot to add... Thanks for the syntax. Works like a charm.

elapsed time in msgbox
 

"Rick" wrote in message
...
I forgot to add... Thanks for the syntax. Works like a charm.

It's a pleasure Rick.

Regards

Bob