Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Create a consecutive list of dates and data from a table of year-month versus day

  1. #1
    Join Date
    11th October 2012
    Posts
    3

    Create a consecutive list of dates and data from a table of year-month versus day

    I have several years worth of daily data arranged in a table. The Column A is the year and month, but in text format (e.g. 1976-08). Row 1 is the day (i.e. 1 through 31). I would like to pull these data into 2 columns: Column A as date (year, month, AND day), and Column B as data. Anyone know a simple and repeatable way to do this?

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    23rd June 2005
    Location
    Ontario, Canada
    Posts
    3,850

    Re: Create a consecutive list of dates and data from a table of year-month versus day

    So you want every single day from 1976 or earlier until today to appear in column A of a new sheet? Then get corresponding data?
    Microsoft MVP - Excel

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  3. #3
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Create a consecutive list of dates and data from a table of year-month versus day

    Welcome to Ozgrid.

    It is generally best to upload a sample workbook (exact structure, dummy data) to provide an accurate context and the content needed to help develop the solution.
    Include a clear and explicit explanation of your requirements, logic, etc., in the workbook and show a few examples of desired results for clarity.

    If you get upload errors due to file size limitation, try using ZIP compression or a file share and provide the link.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  4. #4
    Join Date
    11th October 2012
    Posts
    3

    Re: Create a consecutive list of dates and data from a table of year-month versus day

    NBVC, that's correct. Any ideas?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    23rd June 2005
    Location
    Ontario, Canada
    Posts
    3,850

    Re: Create a consecutive list of dates and data from a table of year-month versus day

    I guess first thing is to get every date in a cell.

    So enter your first date (Jan 1, 1976) in A2 of the new sheet, then select the cell and click, hold and drag down the little black square at bottom right corner of cell... you should see date incrementing... keep going until you get to last date desired.

    Now in B2, enter formula like:

    =INDEX('Sheet1'!$B:$AF,MATCH(TEXT(A2,"yyyy-mm"),'Sheet1'!$A:$A,0),MATCH(DAY(A2),'Sheet1'!$B$1:$AF$1,0))

    where Sheet1 is name of the database sheet... and data is contained in column A:AF.

    make necessary adjustment to references, then copy down.
    Microsoft MVP - Excel

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  6. #6
    Join Date
    11th October 2012
    Posts
    3

    Re: Create a consecutive list of dates and data from a table of year-month versus day

    Awesome NBVC, that worked! Thank you!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Average Data Between Specific Dates (Month, Year)
    By Maze03079 in forum EXCEL HELP
    Replies: 3
    Last Post: February 22nd, 2012, 14:37
  2. Pull Month & Year From Dates
    By SUJU in forum EXCEL HELP
    Replies: 3
    Last Post: October 2nd, 2009, 11:44
  3. Display Month & Year Only Of Dates
    By Ian Howard in forum EXCEL HELP
    Replies: 3
    Last Post: March 14th, 2008, 12:31
  4. Lookup Month & Year In Dates
    By RealEstateFinan in forum EXCEL HELP
    Replies: 8
    Last Post: September 21st, 2007, 11:48
  5. Sorting dates by month rather then year
    By HR Rep in forum EXCEL HELP
    Replies: 2
    Last Post: March 29th, 2005, 22:30

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
  •  
porno