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

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

Thread Status:
Not open for further replies.
Advertisement
1. ### Lisa92Thread Starter

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,202
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

3. ### Lisa92Thread Starter

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.

File size:
51.5 KB
Views:
60
5. ### Lisa92Thread Starter

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

### Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/765904