+ Reply to Thread
Results 1 to 5 of 5

Excel plot date over years

  1. #1
    Registered User
    Join Date
    11-01-2010
    Location
    CapeTown
    MS-Off Ver
    Excel 2010
    Posts
    37

    Excel plot date over years

    Hi

    I am trying to plot dates per year, but have no idea how to do it. I have searched but cannot find anything to help me.

    I want the years on the X-axis and the dates for each year on the Y axis. Is this at all possible on Excel 2019? I am attaching a file with the data.

    Regards
    Attached Files Attached Files

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,941

    Re: Excel plot date over years

    It is not clear what kind of result you want. Do you mean that on the Y axis, you want to have days of the year from Jan 1 - Dec 31, and then for each year show a single day along the Y axis? I am guessing here but see if this has what you need. The dates have to be normalized for a single year to plot that way. Then I created a scatter plot.
    Attached Files Attached Files
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    11-01-2010
    Location
    CapeTown
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Excel plot date over years

    Thank that is exactly what I needed. However at closer glance I have noticed that the vertical gridlines does not align with the X-axis values?
    Attached Files Attached Files
    Last edited by gideone; 04-16-2021 at 03:46 AM. Reason: Closer inspection

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,379

    Re: Excel plot date over years

    Remember that number formatting only changes what you see in the cell, it does not change the underlying value. In this case, the values in column A are not the numbers 2003, 2004, 2005, etc. Rather they are the same date serial numbers as in column B formatted to only show the year portion of the date. The underlying value still retains the month/day (and time of day) information contained in column B.

    If I understand your latest concern, I think you will get what you want by using the YEAR() function in column A. =YEAR(B2) and format as general. See if that is more what you are expecting.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,941

    Re: Excel plot date over years

    I missed that the year was actually a complete date. Here is the implementation of MrShorty's suggestion, and also with the X axis format updated to make the Major intervals 5 years and Minor intervals 1 year.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Add average of past years to plot of current year
    By badeend in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-06-2020, 02:41 AM
  2. please help me add 1, 2 or 3 years to a date in Excel (O365)
    By Steve.west in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-22-2018, 04:24 PM
  3. Excel table date filter not show all years to select
    By ericbsw in forum Excel General
    Replies: 8
    Last Post: 07-21-2017, 04:45 PM
  4. Replies: 4
    Last Post: 11-19-2010, 11:17 AM
  5. Excel =Date and Leap Years
    By thesonofdarwin in forum Excel General
    Replies: 10
    Last Post: 06-15-2010, 03:31 AM
  6. [SOLVED] In EXCEL, how do I make date-time the x-axis of a plot??????
    By hic in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-28-2006, 10:35 PM
  7. Excel Adding years or months to a date
    By Joan in forum Excel General
    Replies: 2
    Last Post: 04-25-2005, 04:06 PM

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