Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How To Make Time Continue

How To Make Time Continue

How To Make Time Continue

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?

RE: How To Make Time Continue

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

RE: How To Make Time Continue

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

RE: How To Make Time Continue

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

RE: How To Make Time Continue

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

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

good luck!

RE: How To Make Time Continue

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.

RE: How To Make Time Continue

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.


RE: How To Make Time Continue

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


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close