# Converting mothly data to daily data

This is a slightly different question to the one answered by Teylyn a little while ago.

In column A are daily dates in format yyyymmdd.  In column L are monthly dates in format Jan-90.  In columns N-R are numbers (1 or 0) relating to each monthly date.  I want to create a column of numbers (in say, columns C-G) for each day that match the respective number in columns N-R for the given month.  So for example, all of the daily numbers in column C for the month of January 1990 in column A would be the same as the number for Jan-90 in column N.

I have attached a spreadsheet as an example. Sample.xlsx
###### Who is Participating?

Microsoft MVP ExcelCommented:
Or with Vlookup

=VLOOKUP(DATE(LEFT(\$A8,4),MID(\$A8,5,2),1),\$L\$5:\$R\$15,MATCH(C\$5,\$L\$5:\$R\$5,0),FALSE)

cheers,
0

Microsoft MVP ExcelCommented:
Hello,

In C8, copy across and down:

=INDEX(\$N\$8:\$R\$14,MATCH(DATE(LEFT(\$A8,4),MID(\$A8,5,2),1),\$L\$8:\$L\$14,0),MATCH(C\$5,\$N\$5:\$R\$5,0))

Adjust the bold parts to reflect the extent of your month lookup table.

cheers, teylyn
0

Commented:
In B8 write this formula - "=DATE(LEFT(A8,4),MID(A8,5,2),1)"

In C8 write this - "=VLOOKUP(\$B8,\$L\$8:\$R\$14,3,FALSE)"

Copy paste the two formulas (all the way down in B column).

When you copy it to D8, change the 3 to 4 ... and so on.

Ss
0

Microsoft MVP ExcelCommented:
Or, even shorter

=VLOOKUP(DATE(LEFT(\$A8,4),MID(\$A8,5,2),1),\$L\$5:\$R\$15,COLUMN(C1),FALSE)

The Column(C1) bit will return 3 in column C. When copied across, the reference will change to D1, E1, etc. returning 4, 5, etc. This will work if your datasets are in the same order in columns C to G and columns N to R.

If you use the formulas with Match() as per above, the order does not matter.

cheers, teylyn
0

Microsoft MVP ExcelCommented:
Hello,

thanks for the grade. Just as an FYI: Questions like these are normally rated with 500 points rather than 125. You will attract more experts if you assign more points to a question.

cheers, teylyn
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.