# Solved: Transferring dates from one workbook to another in Excel

Discussion in 'Business Applications' started by Lisa92, Nov 4, 2008.

Not open for further replies.

Joined:
Nov 4, 2008
Messages:
6
I have a row of dates against a surname and first name in one workbook that I need to organise. For example:

Graham Mitchell 1/12/07 31/2/08 6/6/08
Sam Knowles 1/11/07 6/1/08 4/4/08

I then need to organise them in a different workbook as follows:

November December January February
Graham Mitchell 1/12/07 31/2/08
Sam Knowles 1/11/07 6/1/08

What formula would I use to look at a line of dates in a workbook and put information into the cell if the month & year are the same but leave it blank if it different??

Hope that I explained this okay. I am using Excel 2007 by the way.

Lisa92

2. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,181
First Name:
Wayne
can you post a sample spreadsheet

so you want to create a set of months of columns and then test every date in the row and if the month is the same copy that record to the corresponding month
what about the year, does you dates cross a year so do you have Sep07 and sept08 and how would you handle that

Joined:
Nov 4, 2008
Messages:
6
I have uploaded a sample of the file - I do want to differentiate between the years as well, sorry for not being specific!

File size:
25 KB
Views:
60
4. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
What formula would you use?

Brilliant challenge.

1. Copy Sheet2 into the workbook containing Sheet1

2. Insert a new Sheet3, copy everything from Sheet2 to Sheet3

3. Replace any dates on Sheet3 with this formula:

=Sheet2!D2-DAY(Sheet2!D2)+1

to convert them to "first of the month"s

4. Use this formula in the first cell of the "grid" on Sheet1:

=OFFSET(Sheet2!\$C\$1,MATCH(\$A2,Sheet3!\$A:\$A,0)-1,MATCH(D\$1,INDIRECT("Sheet3!D"&MATCH(\$A2,Sheet3!\$A:\$A,0)&""&MATCH(\$A2,Sheet3!\$A:\$A,0)),0))

and copy down and across.

To see this "in action", open the attached. Select D1 on Sheet1 and copy; select all the yellow cells on Sheet1 and paste.

Notes:

(i) any time you want to dump the #N/As, use F5 > Special > Formulas > Errors > OK; then hit Delete.

(ii) before you delete Sheet2 and Sheet3 (to return wb to original state), select all formulas on Sheet1 and copy; then Edit > Paste Special > Values.

(iii) note one thing in the (Sheet1) formula closely:

&""

it relates to columns used on Sheet2. If you go beyond P, you'll need to adjust.

(iv) the formula uses the MATCH function; you should use (i.e. have) something more ... "unique in nature" than (e.g.) "Michael" to base it on.

HTH

PS: when you've filled in the yellow cells, check the formula results -- since I didn't so much.

#### Attached Files:

• ###### reviews.xls
File size:
51.5 KB
Views:
60

Joined:
Nov 4, 2008
Messages:
6
This is great - it works perfectly.

Thank you.

6. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
YW. Click "Mark Solved" at the top of this page if you wish.

As Seen On