Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

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

1. I agreed to these rules
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. ## 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?

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.

4. I agreed to these rules
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. ## 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.

6. I agreed to these rules
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

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

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