Closed Thread
Results 1 to 8 of 8

Create line on chart showing current day

  1. #1
    Registered User
    Join Date
    02-17-2011
    Location
    Waco, Tx
    MS-Off Ver
    Excel 2010
    Posts
    19

    Create line on chart showing current day

    I have a line chart with various lines of data spanning past and future months, but I'd like to put a vertical black line on the chart showing the current day for reference. I'm creating the chart from a Macro which is working great, but I can't find any way to add just a line for the current date. I really thought this might be a common problem, but I've yet to find any questions or answers related to it. Also I know it's possible as I've seen it done in other Excel spreadsheets, but I don't have access to any of them to see how they did it.

    Update: I've created a sample Excel spreadsheet with the logic I'm using to create the charts in the Macro, but nothing I've tried has come even close to creating a vertical line on the current date to show the current date on the chart. If someone knows how to do this and can demo it on this spreadsheet along with any other notes I'd appreciate the feedback.

    Update 2: I found this showing how to add a line for current date - http://peltiertech.com/Excel/Charts/...ertSeries.html - but given I'm using Excel 2010 I'm not sure how to transpose this from the earlier version. The steps it points out don't seem to apply anymore.

    Take care --

    Sam
    Attached Files Attached Files
    Last edited by samalex; 04-18-2011 at 05:48 PM.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Create line on chart showing current day

    First use a value of 1 in column D to mark day required.

    Use Select Data dialog to add another series using values from column D.
    Move new series to secondary axis.
    Fix secondary vertical axis Min=0 Max=1
    Add Error bars to new series. Use 100 Percent, Minus only, No cap.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    02-17-2011
    Location
    Waco, Tx
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Create line on chart showing current day

    Quote Originally Posted by Andy Pope View Post
    First use a value of 1 in column D to mark day required.

    Use Select Data dialog to add another series using values from column D.
    Move new series to secondary axis.
    Fix secondary vertical axis Min=0 Max=1
    Add Error bars to new series. Use 100 Percent, Minus only, No cap.

    Andy,

    Thanks for the reply, and your rework of my example spreadsheet has exactly what I need. But once I add the new series how do I add it to the secondary axis? I can't find anything on the chart to click or select for the series. When I select the line you added it shows the Format Error Bars, but when creating new series I'm not sure how to get that to come up for it.

    Thanks again for your help --

    Sam

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Create line on chart showing current day

    The new series will infact appear as a single point as the other points are empty.

    Use the ribbon Format tab. In the Selection group is a dropdown box by which you can select and format the series. Axis option is on the firsttab in the dialog.

  5. #5
    Registered User
    Join Date
    02-17-2011
    Location
    Waco, Tx
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Create line on chart showing current day

    Quote Originally Posted by Andy Pope View Post
    The new series will infact appear as a single point as the other points are empty.

    Use the ribbon Format tab. In the Selection group is a dropdown box by which you can select and format the series. Axis option is on the firsttab in the dialog.
    Thanks... I'm slowly learning the ribbon interface in Excel and after finding the Error Bar section I'm good to go. I'm attaching the finalized spreadsheet from my prior example with the code to create the chart with current date in a Macro for anyone interested.

    Take care --

    Sam
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    10-02-2014
    Location
    Brisbane Australia
    MS-Off Ver
    2010
    Posts
    7

    Re: Create line on chart showing current day

    Hi Andy
    I want to be able to have a dynamic line based on todays date as per the attached picture, basically if anyone opens the excel graph on any date, then this date should be highlighted by a dotted or straight line per attached picture, I am not sure how to get this, I followed this thread but still no luck. Can you please help.
    Many thanks in advance
    thassim
    graphDate.jpg

  7. #7
    Registered User
    Join Date
    10-02-2014
    Location
    Brisbane Australia
    MS-Off Ver
    2010
    Posts
    7

    Re: Create line on chart showing current day

    Hi Andy
    I want to be able to have a dynamic line based on todays date as per the attached picture, basically if anyone opens the excel graph on any date, then this date should be highlighted by a dotted or straight line per attached picture, I am not sure how to get this, I followed this thread but still no luck. Can you please help.
    Many thanks in advance
    thassim

    Not sure if the picture got through, here is another version.
    graphDate.jpg

    Attachment 349153

  8. #8
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Create line on chart showing current day

    Welcome to the forum. Pls take some minutes to read forum rules, because:

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1