Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
elapsed time in msgbox
I forgot to add... Thanks for the syntax. Works like a charm.
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time elapsed | Excel Discussion (Misc queries) | |||
Calculate Ending time using Start Time and Elapsed Time | Excel Worksheet Functions | |||
Need Elapsed Time Help | Excel Worksheet Functions | |||
elapsed time | Excel Worksheet Functions | |||
Elapsed Time | Excel Programming |