Ok, board masters........heres one........dealing in time co

jdavis9

Active Member
Joined
Mar 8, 2002
Messages
337
I have vb code/form which will go into a data set, find an event based on a user input, graph 16 columns of data on individual graphs based on the address of the event (example...$b$6300). The user inputs amount of time prior to event at b6300 and the amount of time after the event to plot. All of this works fine.
The problem arrises when I try to place a text box on each graph containing the time of the actual event. The time is located in the column next to the event (a6300). I currently assign the the time to a variable "EVENTTIME". When the time goes in the box, its is in serial format (0-23:59:59 = 0-.99999999).
Question = How do I get the format in the text box (automatically) to be in hh:mm:ss.000 format?
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Mark O'Brien

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
This is just a simple example, do you really need milliseconds? The only way I know to do this includes using an API call:

<pre>
Public Sub datedemo()

Dim EVENTTIME As Date


EVENTTIME = FormatDateTime(Now, vbLongTime)
MsgBox EVENTTIME


End Sub</pre>

HTH
 

jdavis9

Active Member
Joined
Mar 8, 2002
Messages
337
The data comes from a data acquisitions system a 200 samples per second or a delta time of 0.005 seconds. I need the event time within 20 milliseconds, so I could live with display of hh:mm:ss.00. I can get the time on all of the graphs if I type it into a text block. But something about extracting it from the worksheet converts it.
 

jdavis9

Active Member
Joined
Mar 8, 2002
Messages
337
The lookup is as follows

do until activecell.value > event or activecell.value = vbnullstring
activecell.offset(1,0).select
loop

if activecell.value = vbnullstring end sub
EVENTTIME = activcell.offset(0,1).value

Later the value is used to populate a text box.
 

Mark O'Brien

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530

ADVERTISEMENT

Try using the "Text" property of the cell rather than the "Value" property:

e.g.

<pre>
EVENTTIME = activecell.offset(0,1).Text</pre>

HTH
 

Watch MrExcel Video

Forum statistics

Threads
1,123,480
Messages
5,601,911
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top