+ Reply to Thread
Results 1 to 13 of 13

How do I extract data between dates?

  1. 01-25-2018, 02:49 PM #1
    Budhdr
    Budhdr is offline
    Registered User
    Join Date
    01-11-2018
    Location
    New York, New York
    MS-Off Ver
    2010,2013,2016, 2019, 365
    Posts
    24

    How do I extract data between dates?

    Hello I'm trying to extract data based on the dates. For example, I want the data from my Master List to come to my October 2017 sheet only if they were in the month of October. I tried messing around with the formulas and this is what I tried and I keep getting #value. It's long but I felt like I got everything down.
    I want something that could be automatic because this is a datasheet that will be constantly updated.

    =IF(ROWS(A$3:A3)>$H$1,"",INDEX('Master List'!$A$4:$A$1000,SMALL(IF(('Master List'!$A$4:$A$1000>=$C$1)*('Master List'!$A$4:$A$1000<=$E$1),ROW('Master List'!$A$4:$A$1000)-ROW('Master List'!$A$4:$A$1000)+1,ROWS(A$3:A3))))

    I will attached the file I'm working with.
    Attached Files Attached Files
    Last edited by Budhdr; 01-25-2018 at 03:19 PM.
    Reply With QuoteRegister To Reply
  2. 01-25-2018, 02:55 PM #2
    63falcondude
    63falcondude is offline
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: How do I extract data between dates?

    That is an array formula. You have to confirm your formula with Ctrl Shift Enter instead of just Enter.

    i.e. double click on cell A3 then press Ctrl+Shift+Enter.
    Reply With QuoteRegister To Reply
  3. 01-25-2018, 03:00 PM #3
    Budhdr
    Budhdr is offline
    Registered User
    Join Date
    01-11-2018
    Location
    New York, New York
    MS-Off Ver
    2010,2013,2016, 2019, 365
    Posts
    24

    Re: How do I extract data between dates?

    Quote Originally Posted by 63falcondude View Post
    That is an array formula. You have to confirm your formula with Ctrl Shift Enter instead of just Enter.

    i.e. double click on cell A3 then press Ctrl+Shift+Enter.
    I did and Chinese symbols came on.
    Chinese symbol in excel.png
    Reply With QuoteRegister To Reply
  4. 01-25-2018, 03:03 PM #4
    63falcondude
    63falcondude is offline
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: How do I extract data between dates?

    I have no idea why they are formatted like that but the actual value is a date, specifically 10/2/2017. You can change the formatting to show the dates as you expect.
    Reply With QuoteRegister To Reply
  5. 01-25-2018, 03:07 PM #5
    Budhdr
    Budhdr is offline
    Registered User
    Join Date
    01-11-2018
    Location
    New York, New York
    MS-Off Ver
    2010,2013,2016, 2019, 365
    Posts
    24

    Re: How do I extract data between dates?

    Quote Originally Posted by 63falcondude View Post
    I have no idea why they are formatted like that but the actual value is a date, specifically 10/2/2017. You can change the formatting to show the dates as you expect.
    okay I got the first row and it's fine.
    But why is the second row all Chinese.
    Reply With QuoteRegister To Reply
  6. 01-25-2018, 03:18 PM #6
    Budhdr
    Budhdr is offline
    Registered User
    Join Date
    01-11-2018
    Location
    New York, New York
    MS-Off Ver
    2010,2013,2016, 2019, 365
    Posts
    24

    Re: How do I extract data between dates?

    Quote Originally Posted by 63falcondude View Post
    I have no idea why they are formatted like that but the actual value is a date, specifically 10/2/2017. You can change the formatting to show the dates as you expect.
    GOT IT. I have no idea why buy my location was in Chinese for the date format .-.

    +REP for you!
    Reply With QuoteRegister To Reply
  7. 01-25-2018, 03:19 PM #7
    63falcondude
    63falcondude is offline
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: How do I extract data between dates?

    That's strange. Glad you got it figured out.

    Thanks for the rep!
    Reply With QuoteRegister To Reply
  8. 01-25-2018, 03:21 PM #8
    Budhdr
    Budhdr is offline
    Registered User
    Join Date
    01-11-2018
    Location
    New York, New York
    MS-Off Ver
    2010,2013,2016, 2019, 365
    Posts
    24

    Re: How do I extract data between dates?

    Quote Originally Posted by 63falcondude View Post
    I have no idea why they are formatted like that but the actual value is a date, specifically 10/2/2017. You can change the formatting to show the dates as you expect.
    It's solved but out of curiosity. Why can I just drag it to apply the formula everywhere? It's only allowing me to copy vertically and horizontally.
    Reply With QuoteRegister To Reply
  9. 01-25-2018, 03:24 PM #9
    63falcondude
    63falcondude is offline
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: How do I extract data between dates?

    Aah looks like you got to my comment from post #7 before the edit was made...

    I'm not sure what you're asking. In post #5 you said that you got the first row. This means that the correct formulas were in the first row.

    All that you have to do is drag the formulas down as many rows as needed.
    Reply With QuoteRegister To Reply
  10. 01-25-2018, 03:30 PM #10
    Budhdr
    Budhdr is offline
    Registered User
    Join Date
    01-11-2018
    Location
    New York, New York
    MS-Off Ver
    2010,2013,2016, 2019, 365
    Posts
    24

    Re: How do I extract data between dates?

    Quote Originally Posted by 63falcondude View Post
    Aah looks like you got to my comment from post #7 before the edit was made...

    I'm not sure what you're asking. In post #5 you said that you got the first row. This means that the correct formulas were in the first row.

    All that you have to do is drag the formulas down as many rows as needed.
    It won't allow me. Example: In the October 2017 sheet, I can only either drag it down from A3 to A4........ or....... A3 to L3.

    Attach will be the file in case you are confused.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 01-25-2018, 03:38 PM #11
    63falcondude
    63falcondude is offline
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: How do I extract data between dates?

    That's normal. You cannot drag formulas diagonally.

    In this case you would enter the formula in A3, drag that formula through L3, and then with A3:L3 selected, drag the formulas down however far you wanted.
    Reply With QuoteRegister To Reply
  12. 01-25-2018, 07:29 PM #12
    Budhdr
    Budhdr is offline
    Registered User
    Join Date
    01-11-2018
    Location
    New York, New York
    MS-Off Ver
    2010,2013,2016, 2019, 365
    Posts
    24

    Re: How do I extract data between dates?

    Quote Originally Posted by 63falcondude View Post
    That's normal. You cannot drag formulas diagonally.

    In this case you would enter the formula in A3, drag that formula through L3, and then with A3:L3 selected, drag the formulas down however far you wanted.
    ah okay that's actually perfect. Thank you very much!
    Reply With QuoteRegister To Reply
  13. 01-26-2018, 09:12 AM #13
    63falcondude
    63falcondude is offline
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: How do I extract data between dates?

    You're welcome. Happy to help.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Extract data from between two dates
    By ppidgursky in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-19-2017, 11:44 AM
  2. Extract data between two dates
    By Tarentum in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-28-2017, 05:48 PM
  3. [SOLVED] Extract Data Between 2 Dates
    By omagoodness in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-23-2016, 12:48 AM
  4. Extract data between two dates to listbox
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-16-2015, 07:07 PM
  5. Extract data between two dates
    By Ogi in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-24-2014, 07:19 AM
  6. Extract data between two dates
    By grey_hair in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-21-2011, 10:25 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1