1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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. Lisa92

    Lisa92 Thread 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. etaf

    etaf Moderator

    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
     
  3. Lisa92

    Lisa92 Thread 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!
     

    Attached Files:

  4. bomb #21

    bomb #21

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

    Brilliant challenge. (y)

    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)&":p"&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:

    &":p"

    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:

  5. Lisa92

    Lisa92 Thread Starter

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

    Thank you.
     
  6. bomb #21

    bomb #21

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

As Seen On
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.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

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

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice