How To Make Time Continue

[hotdipper]

I am collecting real time data from a PLC diectly into an excel spreadsheet. I also graph the data as collected. I am collecting 24 hr worth of data at a time. My time stamp is in the format of xx:xx:xx XM. My problem is when midnight rolls around my data moves to the o point on the X-axis instead of continuing on. The graph is then hard to follow. Any thoughts on how to make the time simply continue on out?

 

[PennStateIE]

Add the date. mm/dd/yy xx:xx:xx xM

 

[Clyde38]

=NOW() spreadsheet function will return date and time.

 

[rb1957]

the trouble with NOW() is that it is the current time, and changes when you update the s/sheet; unless you copy the value to another cell.

remember that the date is just a number, counting the days from some distant datum (jan 4 1901 or something). fractions of days (like .25) return hours (6am), so maybe you could add today's date when you import data for one day

 

[pmover]

what type of chart is being used to plot the data?

i created demo using a line type chart with no problems.

good luck!
-pmover

 

[hotdipper]

I am using a scatter plot. The problem is actually in my macro. I'm pulling data into excel using a macro that copies it to a line with a time stamp but doesn't give me a date. The only date function I have found in VBA doesn't give the format I need because it doesn't display seconds. I haven't had a chance to try it yet, but I think I can use the now function with a custom format in the cells. I hope this will work.

 

[IRstuff]

If you have access to a cell with NOW(), you can simply do a copy/paste special - value operation to get the current value into any cell you want.

TTFN

 

[brengine]

Did you try using the VBA help? I went to Main Menu\View\Object Browser, left the search location at "<All Libraries>", entered in "time", and then clicked the button with binoculars on it. In search results I found that in the VBA Library there's a DateTime Class that can get what you want. To access the help, select one of the "Member of" topics then hit the ? button in the top/search portion.

Paste the below code into a new macro and run it to see what you get.

Sub Macro1()

    For iCount = 1 To 10
        Cells(iCount, 1) = Now
        Debug.Print Now
    
        PauseTime = 1    ' Set duration.
        Start = Timer    ' Set start time.
        Do While Timer < Start + PauseTime
            ' Yield to other processes.
        Loop
    
    Next iCount

End Sub

In Excel I had to change the Cell Format to Custom and then entered "m/d/yyyy hh:mm:ss AM/PM" to get all the date and time info to display in the cell (note that if you look at the Formula Bar all the info. is there in each cell, it's just the disply format that was rounding off the seconds).

Ken