First of all, sorry about the title. I'm not good at forming questions since I usually just tell my situation to people.
So I'll explain my situation. Let's say that i have a list of dates on column A. Then I enter a "Start date" in "B1" and an "End date" in "B2" What I want is to find all dates that falls between "start date" and "end date".
Any help would be appreciated, or you could show me links to threads with similar subject. I can't do a good search since, as you can see from my title, I don't know the correct keywords for this type of situation.
Check If Date Is Between 2 Dates
-
-
-
Re: Timespan As Criteria For Getting Values
What's your intentions with the found dates?
-
Re: Timespan As Criteria For Getting Values
=AND(A4>$B$1;A4<$B$2) if your dates are in col a
-
Re: Timespan As Criteria For Getting Values
Sicarii:
My first question there was actually kinda like asking a basic example. What I'm actually doing is a summary report based on time periods. I'm gonna use it in a payroll system. I'll try to explain as best as I could without a worksheet, since my worksheets at work.
What my worksheet does is get all necessary information for payroll on consultants based on the time they requested payment. So if their "date of request" is under the "period range"(Stardate-endate), the worksheet will list all payroll information about that particular consultant's request.
I was thinking that as long as I can get the "date of request", I can use it as a lookup criteria to get the other informations.h1h:
for some reason, my excel autocorrects the formula you gave, giving me
=and(A4>A$1:$B4<$B$2). My dates are in column A the startdate in "B1", enddate in "B2" and the formula in "C1". -
Re: Timespan As Criteria For Getting Values
=IF(AND(A4>$B$1,A4<$B$2),A4,"")
I just wasn't sure if there were other columns next to your dates that needed to be grabbed as well or if it would be better to start a VBA solution.
-
-
Re: Timespan As Criteria For Getting Values
Sicarii:
um...that formula you gave only scans "A4", it doesn't scan the whole column. It only shows the answer if "A4" meets the criteria, and it will only show the value in A4. What I want is to for "C1" to display the date in the list that matches the criteria.
-
Re: Timespan As Criteria For Getting Values
Drag it up/Drag it down...only displays if the date is inbetween start/end. I think you can then do another formula that would take that column with intermittent dates and remove the blanks giving a new column the unique dates without blanks (if that made sense)
-
Re: Timespan As Criteria For Getting Values
have a look at the attached
an extra column is added in front of former col a. here we check if the date falls inbewteen the given range. C1 then looks up the first accurance of true. -
Re: Timespan As Criteria For Getting Values
miko,
QuoteMy first question there was actually kinda like asking a basic example.
The rules state that you kinda like explain everything in your first post!
-
Re: Timespan As Criteria For Getting Values
BytheCringe2:
Sorry... I was thinking it's easier to understand if I just give a simple example of my problem. I'm thinking I'll just get the basic solution to my problem, then just modify or make the formula more complex to fit my worksheet. Also, though I don't know about most posters here, I thought that by making the first post as simple as possible, it'll help other searchers to quickly understand what my threads are about. There are time when I go to a thread the first post is so long explaining everything, that by the time I'm finished I had already forgotten what was main problem,but that's just me.:smile:Anyway Sicarii's example gave me an idea on how to solve the problem. It's already past midnight here and my head is aching from lack of sleep already, so I'll just try that tomorrow.
-
-
Re: Timespan As Criteria For Getting Values
Miko, You are OK giving a simple example, as well, as long as you include the full problem. It is very frustrating to a helper to work for a solution that fits what you have given, only to be told it doesn't work because that was just a sample.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!