Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Extracting information between two dates
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2017
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Extracting information between two dates

    I researched this and found a formula I believe should work, but for whatever reason, it's not... What am I missing?

    I have a list of my employees on Sheet2, and each subsequent sheet has the data for the hours for each employee (Sheet3 will be employee A, Sheet4 for employee B). I'm importing this from Quickbooks Online, so each employee has to have their own Worksheet with their hours.

    For the employee worksheets, the hours are in G:G, and the dates are in B:B for each employee. On the summary sheet (Sheet2), the date range is B1 for the start date and C1 for the end date. I'm working with cell C2 to give me the result of hours worked between the date range. I used this formula that I found:

    =SUMIFS(Hours,Date,">="&B$1,Date,"<="&C$1)

    I made ranges for this as this is how I found it on another site, but I'd rather steer away from ranges since I'm sure we'll hire more employees as we go and I want to make THAT process easier.

    Anyway, when I use this formula, the result I get is 0.00. The best I can figure is that the hours are not tied to the date, so Excel returns the 0.00 result. How can I make this work?

    Thanks!

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,850
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Extracting information between two dates

    ranges can be modified in name manager, so that it is obvious what you are updating. The issue potentially is that things like hour and date are used by excel, you may do better with bob and tom
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,071
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Extracting information between two dates

    @rallysport096, your formula works fine for me. In the employee sheet in the first cell in a spare column can you put the formula
    =ISNUMBER(B1)
    and drag down then post whether you get TRUE or FALSE.

    Edit: I also would change the names you are using for the ranges but it doesn't cause an issue for me with your formula.
    Last edited by MARK858; Apr 23rd, 2017 at 03:46 AM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  4. #4
    New Member
    Join Date
    Apr 2017
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting information between two dates

    Quote Originally Posted by MARK858 View Post
    @rallysport096, your formula works fine for me. In the employee sheet in the first cell in a spare column can you put the formula and drag down then post whether you get TRUE or FALSE.

    Edit: I also would change the names you are using for the ranges but it doesn't cause an issue for me with your formula.
    I only used the range names mentioned as an example for the post (to protect Employee info). Thanks for the suggestion.

    BTW, you're a rockstar. I ran the ISNUMBER function on the employee data sheet and discovered Quickbooks exports the date as plain text. I ran a column to the side with the DATEVALUE function (as I will have to continue exporting from QB) and it worked after that. Than you so much!

  5. #5
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,071
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Extracting information between two dates

    You're welcome, you will probably find that you could select the offending column then click Data, click Text to Columns, click Next twice, check that General is selected and finally click Finish and it will put the column straight.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  6. #6
    New Member
    Join Date
    Apr 2017
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting information between two dates

    Quote Originally Posted by MARK858 View Post
    You're welcome, you will probably find that you could select the offending column then click Data, click Text to Columns, click Next twice, check that General is selected and finally click Finish and it will put the column straight.
    That works as well. However, when I import further reports from QB, will I have to do that each time or can it be set to automatically fix it?
    Last edited by rallysport096; Apr 23rd, 2017 at 04:25 AM.

  7. #7
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,850
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Extracting information between two dates

    you can change the text value to a number by adding double minus

    =A1 =--A1, then regardless of the download, the formula can correct
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  8. #8
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,071
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Extracting information between two dates

    @mole999, are you proposing changing the individual values in a separate column as the OP is already doing with Datevalue or amending the formula
    =SUMIFS(Hours,Date,">="&B$1,Date,"<="&C$1)
    in some way where the problem cells are in the Date named range?

    If it is the latter can you post the proposed formula?
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  9. #9
    New Member
    Join Date
    Apr 2017
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting information between two dates

    Quote Originally Posted by mole999 View Post
    you can change the text value to a number by adding double minus

    =A1 =--A1, then regardless of the download, the formula can correct
    Amazed you all know this. Or that this was even developed in the first place .

    The function I'm using now looks like this:

    =IF($F$2="Custom",SUMIFS('Employee Name'!G:G,'Employee Name'!J:J,">="&N$2,'Employee Name'!J:J,"<="&O$2),SUMIFS('Employee Name'!G:G,'Employee Name'!J:J,">="&H$2,'Employee Name'!J:J,"<="&I$2))
    How could I apply the "=--A1" suggestion to that (where J:J would change to B:B, as QB enters the date in that column)? Also, is there any way to clean this up? I'd rather not use the sheet name for every argument, but rather refer to a cell on the same sheet with the same name as the worksheet I'm directing to. Example, E4, which has the employee's name (same as the name of the worksheet), could replace 'Employee Name'!.

    *Edit - I was trying some =INDIRECT functions but with no luck. Was I on the right track?
    Last edited by rallysport096; Apr 23rd, 2017 at 05:06 AM.

  10. #10
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,850
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Extracting information between two dates

    Quote Originally Posted by MARK858 View Post
    @mole999, are you proposing changing the individual values in a separate column as the OP is already doing with Datevalue or amending the formula in some way where the problem cells are in the Date named range?

    If it is the latter can you post the proposed formula?
    Just the formula thats reading the text data
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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