elapsed time in msgbox

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

HTH

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