+ Reply to Thread
Results 1 to 20 of 20

Between 2 Dates Problem

  1. #1
    Registered User
    Join Date
    04-11-2008
    Posts
    17

    Red face Between 2 Dates Problem

    Hey all you all helped me with a formula for Between 2 dates. However this formula is removing the last day of that date range. Here is the Formula:

    Please Login or Register  to view this content.
    It will not work for a single day either. For instance

    From: 01/01/2009 To: 01/01/2009 = Rusults in 0 Returned Records.

    From 01/01/2009 To: 03/31/2009 = Results is missing day 03/31/2009 Data.

    Thanks and hope there is an easy fix for this.
    Last edited by TRI0N; 12-01-2009 at 05:51 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Between 2 Dates Problem

    Should work... can you attach sample workbook?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    04-11-2008
    Posts
    17

    Re: Between 2 Dates Problem

    Unfortuanlly I can't. Its government property. The formula works but if the Start and End Date are the same it results to 0 and if between dates the ending is not added to the results. It would seam silly to have users enter 04/01/2009 just to have it ad in 03/31/2009. Its just not adding the ending day to its function.

  4. #4
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Between 2 Dates Problem

    create a dummy workbook that includes enough dummy data to reproduce the problem and upload that.

    or

    check the data in your original file

    is ('Transaction DB'!$D:$D=B41) TRUE?

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Between 2 Dates Problem

    Well, you have the >= and <= operators so it should theoretically work... So unless something is wrong with the actual date entries in those ranges or something is not right in D:D or E:E then it is hard to tell without seeing it.

    Can you post the workbook, first deleting unnecessary columns and changing around names, etc that shouldn't be seen?

  6. #6
    Registered User
    Join Date
    04-11-2008
    Posts
    17

    Re: Between 2 Dates Problem

    Okay making Dummy Book. Need to remove, DNS Connections, Network Data Paths, Etc.. Give me a few.

  7. #7
    Registered User
    Join Date
    04-11-2008
    Posts
    17

    Re: Between 2 Dates Problem

    Okay I'm making a Test Book but it takes some time to remove data I don't need to add. While I'm at it the date that the forumla is run against has time in it. Perhaps the solution would be to add time in the formula. Such as Start: 0:00:00 and End 23:59:59? If that is the case then how would I add just that to what I have?

    I believe perhaps the formula is automatically making the date times 0:00:00 so the End Date is not added due to this since it will stop adding at 0:00:00 that day.
    Last edited by TRI0N; 12-01-2009 at 03:35 PM.

  8. #8
    Registered User
    Join Date
    04-11-2008
    Posts
    17

    Re: Between 2 Dates Problem

    Please Login or Register  to view this content.
    Since it should default to 0:00:00 for the start date I don't need to add a start time. But would the above for End Time be correct?

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Between 2 Dates Problem

    Try:

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    04-11-2008
    Posts
    17

    Re: Between 2 Dates Problem

    Okay that can't be right. Does not see the time correctly. Hummm mind is going bonkers.

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Between 2 Dates Problem

    It's looking only at the date portion that's with the INT() function, since you are trying to equate to the DATE() function.

  12. #12
    Registered User
    Join Date
    04-11-2008
    Posts
    17

    Re: Between 2 Dates Problem

    No I mean the formula is not valid it see the Time in the end date as a range on the current page.

  13. #13
    Registered User
    Join Date
    04-11-2008
    Posts
    17

    Re: Between 2 Dates Problem

    Demo File Uploaded. Sorry for the delay.. There was a lot to remove before I could do this.
    Attached Files Attached Files

  14. #14
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Between 2 Dates Problem

    I hadn't noticed you add that 23:59:59 in there...

    try

    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    04-11-2008
    Posts
    17

    Re: Between 2 Dates Problem

    Play with D41 on Rounds Detailed to get results of 34.

  16. #16
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Between 2 Dates Problem

    I get 34 with this:

    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    04-11-2008
    Posts
    17

    Re: Between 2 Dates Problem

    Rock On! Will impliment that.. Was like going nuts on this.. Overlooked the Time Default to date and that explains the day missing. But adding time was like OMG.. Doh!

  18. #18
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Between 2 Dates Problem

    Great!

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

  19. #19
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Between 2 Dates Problem

    You should really be looking to use SUMIFS rather than SUMPRODUCT - it will be significantly more efficient

  20. #20
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Between 2 Dates Problem

    Then:

    Please Login or Register  to view this content.

+ 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