Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

  • #2
    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?
    Where there is a will there are many ways. Finding one that works for you is the challenge!

    MS Excel MVP 2010-2016

    Comment


    • #3
      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

      Comment


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

        NBVC, that's correct. Any ideas?

        Comment


        • #5
          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.
          Where there is a will there are many ways. Finding one that works for you is the challenge!

          MS Excel MVP 2010-2016

          Comment


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

            Awesome NBVC, that worked! Thank you!

            Comment

            Working...
            X