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:
It will not work for a single day either. For instance
Please Login or Register to view this content.
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.
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.
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.
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?
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?
Okay making Dummy Book. Need to remove, DNS Connections, Network Data Paths, Etc.. Give me a few.
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.
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?Please Login or Register to view this content.
Try:
Please Login or Register to view this content.
Okay that can't be right. Does not see the time correctly. Hummm mind is going bonkers.
It's looking only at the date portion that's with the INT() function, since you are trying to equate to the DATE() function.
No I mean the formula is not valid it see the Time in the end date as a range on the current page.
Demo File Uploaded. Sorry for the delay.. There was a lot to remove before I could do this.
I hadn't noticed you add that 23:59:59 in there...
try
Please Login or Register to view this content.
Play with D41 on Rounds Detailed to get results of 34.
I get 34 with this:
Please Login or Register to view this content.
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!
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
You should really be looking to use SUMIFS rather than SUMPRODUCT - it will be significantly more efficient
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Then:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks