elapsed time in msgbox

R

Rick

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.
 
B

Bob Phillips

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 said:
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).
Click to expand...
In a worksheet it's no problem,
"=DATEDIF($A$4,NOW(),""y"")&"" years, ""&DATEDIF($A$4,NOW(),""ym"")&""
Click to expand...
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
Click to expand...
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.
 
R

Rick

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...
 
B

Bob Phillips

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 said:
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
Click to expand...
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...
 
R

Rick

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.
 
B

Bob Phillips

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 said:
If you put the formula into a cell

=DATEDIF($A$4,NOW(),""y"")&"" years, ""&DATEDIF($A$4,NOW(),""ym"")&""
Click to expand...
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
Click to expand...
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.
 
B

Bob Phillips

--

HTH

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