Results 1 to 7 of 7
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Date Format Graph (Excel/ SQL Server)

    Background
    I have created a SQL procedure that feeds updates a custom table in my SQL 2000 database. The table holds very simple data iRecordId, dtTime, iCount all it does is count the current number of people logged on at anyone time minute by minute.

    I will be running this over the period of one week so that I can monitor licence usage.

    Question
    I have created a database query in Excel, a simple macro to run the update to that query and want to do a chart. The data extracts as follows:


    1 15/01/2007 14:15 1984
    2 15/01/2007 14:16 1985
    3 15/01/2007 14:17 1991
    etc

    As it will be running from 15/1 to 22/1 Iwould like the graph to have to notations on the x-axis. During the 15th I want to say the date and then the hours and then 16/1 and the hours. I want it still to graph minute to minute...I think that is clear...the formatting of the graph is my problem
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Format Graph (Excel/ SQL Server)

    You can use a line chart with a category scale for the category axis, not a time scale (which is the default here). This is set in Chart | Chart Options, Axes tab. Set the number of categories between tick mark labels and between tick marks to 60 (60 minutes = 1 hour). Adjust the alignment of the labels for readability.

    Or use an XY scatter chart. Set the primary unit for the x axis to 0.0416666666666666 (1/24th of a day). Adjust the alignment of the labels for readability.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Format Graph (Excel/ SQL Server)

    Hi Hans

    Having a late night here...where am I going wrong?
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Format Graph (Excel/ SQL Server)

    Sorry, should have written Major unit instead of Primary unit (incorrect translatiom from Dutch back into English). Set the Major unit to 0.04166666666666666 (and the Minor unit too, but that isn't important)

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Format Graph (Excel/ SQL Server)

    Thanks got it...

    OK last one for the night.....I have a macro that updates the data and then refreshes the graph every 5 minutes ( although it says every minute I don't want my laptop over working) I have put together a really hammy bit of code to refresh it...is there a better way <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>


    Private Sub Workbook_Open()
    10 UpdateChart

    Application.Wait (Now + TimeValue("0:05:00"))

    GoTo 10

    End Sub
    Jerry

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Format Graph (Excel/ SQL Server)

    You could have Workbook_Open call a public sub in a standard module that calls itself using Application.OnTime:

    In ThisWorkbook:

    Private Sub Workbook_Open
    Call UpdateChart
    End Sub

    In a standard module:

    Public Sub UpdateChart()
    ' Code to update chart
    ...
    Application.OnTime Now + TimeValue("0:05:00"), "UpdateChart"
    End Sub

    Not really much different, but it avoids the evil GoTo, and takes the code out of ThisWorkbook except for one call to a procedure.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Format Graph (Excel/ SQL Server)

    Lovely, thanks for turning it on its head, I think that will do the job nicely
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •