+ Reply to Thread
Results 1 to 13 of 13

How to extract dates from cells and list them seperately in a new tab?

  1. #1
    Registered User
    Join Date
    05-11-2009
    Location
    europe
    MS-Off Ver
    Excel 2003
    Posts
    9

    How to extract dates from cells and list them seperately in a new tab?

    Hi guys,

    I have set up a 'week of' calendar for a year. This allows users to enter data in a cell for the week. For example, the today would be displayed on the calendar, the 11th.

    What I would like is, in a separate tab, to list (in list format) only dates with data entered.

    How can I write a formula that checks a cell, and displays the data, but if there is no data, it goes to the next cell (to the right). In the new tab, I would like the data to be listed top to bottom even though the calendar is left to right.

    It would also be cool if in a separate column in the new tab to display the date, but im not sure how because the calendar isn't of actual dates, but of a few cells on the top with the month, and the actual number is in the cell.

    Right now I have this as a start, but it's not much...
    =IF(Category!C11>"",Category!C11,"")

    I have attached an image of how the calendar is set up

    Any help would be great!
    Attached Images Attached Images
    Last edited by cp400; 05-12-2009 at 04:21 PM.

  2. #2
    Registered User
    Join Date
    05-11-2009
    Location
    europe
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: How to extract dates from cells and list them seperately in a new tab?

    any help ?

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to extract dates from cells and list them seperately in a new tab?

    Please don't post pictures when you can post sample workbook that actually make it easy to help you.

    On the uploaded sheet, include some (all?) of these added "new tabs" that you want with adequate mockup of what you're after as a goal. Try not to use the same text string "data" over and over in an example as that makes it hard to see if what you're doing is working correctly or not. Use unique values for things like that, too.

    Make it easy on us, OK?
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    05-11-2009
    Location
    europe
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: How to extract dates from cells and list them seperately in a new tab?

    ok, sorry about that. what is the best way? i tried to save as html and paste it but there were too many characters

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to extract dates from cells and list them seperately in a new tab?

    Excel Forum. Post Excel workbooks.

  6. #6
    Registered User
    Join Date
    05-11-2009
    Location
    europe
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: How to extract dates from cells and list them seperately in a new tab?

    sorry I couldn't get it to work. I've attached a simple sample workbook. I would like to get the entries from sheet1 to be listed in the corresponding columns in sheet 2. if possible, list a date 6 weeks prior to give a warning of the upcoming event.

    again, I would appreciate any help
    Attached Files Attached Files

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to extract dates from cells and list them seperately in a new tab?

    Like so, perhaps.

    The far column for ALERTS will turn yellow on its own when the dates in that column come up. I set them to turn yellow 6 weeks prior.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    05-11-2009
    Location
    europe
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: How to extract dates from cells and list them seperately in a new tab?

    JB, You rock man!! This is awesome.

    I haven't fully digested it but I think this is like perfect!

    Thanks a lot

    If I think of something I hope you wont mind me asking, but this basically does it all!

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to extract dates from cells and list them seperately in a new tab?

    No, don't mind at all. If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED].



    (Also, use the "scales" icon in our posts to leave Feedback, it is appreciated)
    Last edited by JBeaucaire; 05-12-2009 at 04:05 PM.

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to extract dates from cells and list them seperately in a new tab?

    A parting note... I changed the values on the original sheet to ACTUAL dates in the row you originally just had numbers for dates. I entered a real date then just formatted the row to show day only [d]. This way I can compare/match the dates on sheet2 to the dates in that row on Sheet1.

  11. #11
    Registered User
    Join Date
    05-11-2009
    Location
    europe
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: How to extract dates from cells and list them seperately in a new tab?

    don't worry I repped

    thanks again, and thanks for the heads-up on the dates- makes sense now.

    I may be in touch

  12. #12
    Registered User
    Join Date
    05-11-2009
    Location
    europe
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: How to extract dates from cells and list them seperately in a new tab?

    Yo JB, I'm trying to apply this to another calendar I have.

    In the second tab, how did you get the cells (dates) with no entries to show up blank? Mine keep showing up as 0s.

  13. #13
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to extract dates from cells and list them seperately in a new tab?

    Tools > Options > View > [ ] Zero Values

    Uncheck that field. That particular item is a sheet-by-sheet setting, so it won't effect your other sheets.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1