Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Formula For Determining If Two Date Columns Fall Within Specific Date Range


Let's say I have thousands of employees, but I need to determine who worked for me during a particular date range, and all I have to go on is their start date in one column and their end date in another column.

If:

A1 contains beginning date of employment
B1 contains ending date of employment
C1 contains specified beginning date (criteria)
D1 contains specified ending date (criteria)


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Formula That Will Determine The Number Of Days That Fall In A Specific Month Based On A Date Range
I'm needing a formula that will determine the number of days that fall in a specific month based on a date range. For example, if I have a date range of 10/15/2009 to 01/13/2009, I need the formula to determine the number of days in each month within the range (October has 15 days in the date range; November has 30, December has 31, and January has 13.) I have a large spreadsheet that would be so much easier to manage with such a formula. Currently, my spreadsheet is setup as follows. I need the forumla automatically fill in the number of days under each month.

Stard Date End Date Oct-09 Nov-09 Jan-10 Feb-10
10/15/2009 01/13/2009

I'm using Excel 2007.

View Replies!   View Related
REcord That Fall Within Date Range, AND Meet Text Criteria
I have a large database that is updated daily. From within the database I need to:

1. Select all records where date field A is 14 days or less than date field B

AND

2. Where a field C matches a text criteria, i.e., =DOGGIE

AND

3. Append KITTY to field D of all records that match criteria 1 and 2

Finally

4. SAVE results as a text file.

View Replies!   View Related
Only Countr Cell Values That Fall Within A Specific Range
I need to keep track of tardy occurences, but I only have to consider occurrences that have occurred in the past six months from the current day. Column A contains the names of the employees, Column C thru IV contains daily dates beginning with 1/1/2007. Column B contains a CountIf function to count the number of times T appears in columns c - iv.

View Replies!   View Related
How To Calculate Date Fall In Which Week
Need a solution for calcuate a week in user enterd date?

Example

A1 A2
07/01/2009 1
07/01/2009 5

View Replies!   View Related
Determining Date Ranges
I have about 20 files with check posting dates stretched over several years. Maybe a million checks total. The company uses 13 four week periods each year, not a calendar month/year. I need to look up each posting date and attach the correct period to it.

I could do a VLOOKUP with one row for each day of the year, but that would require 2 x 365 row's for just two years. There must be a more elegant way, maybe using an array or some IF statement.

Attached is a sample file, xlsx, with the perior/year info and sample posting date info. Can someone point me in the right direction so that I end up with the correct period/year in column 'C' for the applicable posting date?

View Replies!   View Related
IF Date Formula: IF Formula, That States If A Date Is More Than A Month After The Date In The Cell Then It Is Timely
I need an IF formula, that states if a date is more than a month after the date in the cell then it is timely and if it isn't then it is not timely. For example the date in A1 is 12/11/05, if the cell is A2 is 13/12/05 (or any date after that) then A3 should read Not Timely, if A2 is 12/12/05 (or any date before that, including a date before the date in A1) then A3 should read Timely. Is there any way to do this?? At the moment I am having to go through manually and put in either Timely or Not Timely.

View Replies!   View Related
Date Range: Reference A Single Date And Output Date Ranges
I need to create formulas that reference a single date and output date ranges. The objective is to have a person input a Monday date in any given month and receive a four weeks out worth of dates and ranges. For example: In a lone cell, the person inputs 10/13/08. Automatically, the sheet produces the next full week range: October 19 October 25 in a single cell and also produces a cell for each date. Example: Sunday 19, Monday 20, Tuesday 21, etc. It should look like:

Monday Date:
10/13/08
October 19 October 25
Sunday 19
Monday 20
Tuesday 21
Wednesday 22
Thursday 23
Friday 24
Saturday 25

and then repeat for three more weeks. I thought I had it figured out until the month changed. The dates continued in October instead of adding a month. This report will be ran weekly, so simply adding a +1MONTH to some cells will not benefit me as Ill have to change the formula every week. I want the formula to compute the data without any manipulation over the next several years. The only change will be the Monday date.

View Replies!   View Related
Counting Occurences Within Specific Date Range
Im looking for a formula that will count the number of occurrences of YES in column A between a specific date range (column B) BUT only if the it's categorized as "LAB" (column C).

Up until now Ive jerry rigged the spread sheet to do it with various filters, if, and countif formulas but Im looking for something that will fit in one cell and lower the overall size of the file.

To make it even more complicated Id like to feed the formula the date range information from another editable cell so that it can basically be queried for whatever dates my boss is concerned about at that second.

View Replies!   View Related
Automatically Date & Time Stamp Changes To Specific Range
i need to put a date stamp when a change is made in b3:b31 into e3:e31 for each row also i need to put a date stamp into g3:g31 when a change is made in F3:f31 i try to use 1 "worksheet-change" and it is fine once i use 2 i get Ambiguous Name Detected errors

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("b3:b31"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 3).ClearContents
Else
With .Offset(0, 3) ............

View Replies!   View Related
Minimum And Maximum Values Within Specific Date Range
I have a table of dates and values and would very much like to know a formula for returning the min and mean values for each column within a specified time (date) period.

Table is as follows

Date value(a) Value (b) ....
1/1/09 10 8
2/1/09 8 6
3/1/09 5 2

View Replies!   View Related
Automatic Current Date Formula Extracts From Specific Cell
formula that can automatically use the current month date to extract information from other cells on my spreadsheet.

For example: This formula should be located in cell C6. Cells D6, F6, H6, J6, L6, N6, P6, R6, T6, V6, X6, and Z6 (these cells correspond to the months of the year, January to December respectively) contain the values I need. The month names are labeled above in row five.

Let's say the current month is March, I need the formula in C6 to automatically know that it is the month of March and to pull the information from cell H6 (which is the March cell). Then when April comes along, it will know that it is April and to only pull the information from cell J6; and so on until the end of the year.

View Replies!   View Related
Date/Month :: Specific Date Falls In?
I have a column with dates populated(examp.Fri, 15 Feb 08)in it. In the next column I need to return the month and year (month,year format) this specific date falls in?

View Replies!   View Related
Formula- Two Columns One Contains A Date The Other Contains $
I have two columns One contains a date the other contains $

I want to add the $ column if the Date column contains a date greater than 12-1-06. If the date is not greater than 12-1-06 I don't want it to count.

View Replies!   View Related
Hide/Unhide Columns By Date Range
I have a spreadsheet with a number of sheets two of which contain tables with many columns with a date heading, I would like a means for the user to select a range of dates and for the spreadsheet to automatically hide any columns that don't fall within this range.

View Replies!   View Related
Copy Date Heading Columns To Sheet Named Same As Date
Did anyone use the above tool ? Can we use a macro to launch that tool to search for words in a excel cell ?

View Replies!   View Related
Date Range FORMULA
In sheet 2 I have dynamic values that are constanly changing.

In sheet 1 I have links linking to sheet 2.
ex. =IF(ISBLANK(Sheet2!H3)," ",Sheet2!H3)

I want to alter the formula in a way that it does the following:

It should only put sheet2!H3 if the date is January 12 and the time is between 08.00 -17.00. If the current date/time lies outside this range it should give blank (" ").

View Replies!   View Related
Counting Dates That Fall In A Specific Week Of The Month
Please see the attached xls file so see what I am referring to.

I have shipments that are going to different destinations (rotterdam, austria, london, etc.)

I would like to count the arrival dates in column H that fall under each week's span, but ONLY IF its corresponding value in column F is 'rotterdam'.

Column C contains the ideal numbers that I would like column B's formula to return. I plan on doing this for the entire year, but if someone can some up with a formula, I might be able to modify it for the rest.

Note: This is only an example spreadsheet, I am going to be referencing an external file with much more information on it.

View Replies!   View Related
Compare Dates To Date Range & Return Date Based On Outcome
I need to compare three cells of random dates shown in Column E, F,& G with Row's H5:AK5, H7:AK7, H9:AK7 (the Dates to these rows is Static on row H3:AK3.) EX: ROW 5 has Start Date, End Date (1) and End Date (2). Compare Cell H3 between Start Date & End Date (1). If H3 falls between or equal to Start and End Date(1) then highlight cell H5. Proceed till AK3 (higlighting only the cells H5:AK5). Then compare cell H3 between or equal to End Date (1) and End Date (2) (higlighting only the cells H5:AK5). Then do the same for ROW 7 and ROW 9.

To make things a bit more difficult I need to have " WK#" in Row 14 (these WK# is on another tab called "Task" of the workbook) needs to be displayed in Row's H4:AK4, H6:AK6, & H8:AK8. EX: Compare Date in D15 between or equal to Start Date & End Date (1) then display Wk# in D14 in H4. Continue till all dates in
D15:Z15 are compared to Start Date & End Date (1) and WK#'s in Row D14:Z14 are inputted if applicable in Row H4:AK4, H6:AK6, H8:AK8. I hope this is not confusing. I can't seem to use the upload option so here is alink to download a jpg of the sheet

View Replies!   View Related
Formula To Indicate Date Range On A Calendar
I have attached a file which indicates what I am trying to achieve.
The first question is can this be done and then who do I set it u

View Replies!   View Related
SUM By Both A Specific Month, And A Specific Year From A Single Date Field
So, this works perfectly by itself: {= SUM(IF(MONTH(I4:I17)=1,G4:G17,0))}
And this works perfectly by itself: {=SUM(IF(YEAR(I4:I17)=2007,G4:G17,0))}

But this doesn't work at all: {=SUM(IF(AND(MONTH(I4:I17)=1,YEAR(I4:I17)=2007),G4:G17,0))}

SUM by both a specific month, and a specific year from a single date field?

View Replies!   View Related
Determining Number Of Specific Days In Period
I am wondering if there is a way in XL2003 to determine how many times specific days of the month occur within a specified date range. For example, I need to find out how many times both the 1st of the month, and how many times the 15th of the month occur in the time period starting 05/02/2009, and ending 07/12/2009.

View Replies!   View Related
Restrict Date Entry To Be With Date Range/Scope
I'm having a problem with data validation. I set an entire column so that it could only be a date between the first date in the list and the current date. I tested a few cells to make sure that it was working the way I wanted and I noticed that it allows a random number like 3 or 5 to be input after row 50. This would create a date in the 1900s. Why is this happening and how can I stop it.


View Replies!   View Related
Fill Table Based On Another Within Date Range & Cut Off Date
I have an 'existing results table' as per my attached sample.

I have had help previously from this forum to create lists of 'sold' stock within date ranges (tax year periods) and these are represented as 'sold list' in my attached sample.

I now need to create a list of 'unsold stock' for each annual tax year end date; i.e. populate my table with items that have been created before the end of the date range and that have not been sold by the end of the date range.

Please can someone show me the formula on my attached sample?

Please see my example in red.

I am working with Excel07 however my attached sample is in Excel03 because I couldn't upload an XLSX file.

View Replies!   View Related
Delete Rows If Cell Contains Date Outside Date Range
I've found similar queries on the site, but none seem to handle multiple dates, and my attempts so far have been futile!

Requirement:
I press a button on Sheet1 and am prompted to enter a start date and an end date.

Depending on the dates I enter, all rows on Sheet2 which fall outside the Start/End date range will be deleted.

All dates to be checked against on Sheet2 are in a single column, and all rows are populated, there are no blanks. Dates are in the format dd/mm/yy

View Replies!   View Related
Week Number For Date Relative To Date Range
Is there a UDF that can determine the number of weeks for a date range specific that is not relative to the week number for the year but for the date range itself. i am aware of the weeknum function but this is for week number relative to the year. eg. date range 01/03/2008 - 31/05/2008 has approx 12 weeks and 14/05/2008 will be week number 10 for the range.

View Replies!   View Related
Formula To Count Occurrences In A Given Date Range
I am needing to count the number of calls in a call center between certain hours by date. Column A is the date, Column B is the time of the call. I am needing to know how many calls occurred on X date between the hours of y and z. I have tried various forms of the countif and sumproduct but can't seem to get any of them to work for me. Here is the graph I am trying to populate:


Date Hours
12a-4a 4a-8a 8a-12p 12p-4p 4p-8p 8p-12a
12-7-09
12-8-09
12-9-09
12-10-09
12-11-09


View Replies!   View Related
IF Then, Two Criteria, Date/Time Range Formula
I am having a hard time with this formula. How can I adjust my formula so that everything that falls outside the boundries of my given date range will have the word "DELETE" next to it? Here is what I have and it is not working....

View Replies!   View Related
Date Formula: Date Another Cell Rounds Up To Jan 1st Of The Next Year
what formula could I use when I type in a date another cell rounds up to Jan 1st of the next year? ie 2/1/07 & in another cell it returns 1/1/08.

View Replies!   View Related
Formula For Date Minus Date = Number Of Days
I was wondering if anyone knows how I could enter a date in one cell, then another date in a second cell and in a third cell have it have it so it minuses the first date from the second and calculates the difference outputted in number of days.

Example: 04/31/08 - 04/01/08 = 30 (days)

View Replies!   View Related
Formula Date Result Showing Number Not Date
Im trying to resolve this issue and would appreciate some help.

Scenario
I have a tracking sheet that tracks development of work to be completed by individuals.
I have a due date column that shows when the work should be completed.
Once the work has been completed, the user enters his completion date.

I have a formula that provides an overdue warning (Completion date todays date) and some conditional formatting.

Problem
When I copy the formula through all the cells in the column I get a number (example 39504) and this changes everyday.

How can I eliminate this being shown as it throws out my average development day calculation?

View Replies!   View Related
Date Formula With Variable Date & Comparison Operator
I have a date in cell A1. I have an operator in cell A2. This info is then merged in to cell A3 with the following formula: =A2&TEXT(A1,"dd/mm/yyyy"). My data range is called "wc_date_of_loss". I want to use this info in a SUMPRODUCT formula (as the dates and operators can be changed by the end user). What's the correct syntax for that?

=SUMPRODUCT(--(wc_date_of_loss&A3))
=SUMPRODUCT(--(wc_date_of_loss=A3))
=SUMPRODUCT(--(wc_date_of_loss=TEXT(A2&A1,"general,dd/mm/yyyy")))

View Replies!   View Related
VBA Formula; Sum Up Part Of A Column Based On A Date Range
I am trying to write a formula with some variables passed into it. i want to sum up part of a column based on a date range (i've got the range already).

View Replies!   View Related
Search Table For Date In Date Range
I need to create a table of dates which is structured as such:

A B
1/7/08 Yes
7/7/08 no
25/7/08 yes

Column A is date, Column B is a binary selection (yes, no)

I then have a separate sheet with rows corresponding to each date in July 08. I need a function which will look up the date for each row with the above date range and return the value of column B.

for example - if the date is between 1/7/08 & 6/7/08, it would return Yes. If the date is between 7/7 & 25/7/08 it would return No.

The above table is not fixed and can have many dates, with no pattern for the dates. I though of using a nexted IF statement but it would become a bit clunky.


View Replies!   View Related
Sorting Data By Date Within A Date Range
Hello, I have a question about sorting information using dates but using a range. I need this to sort when I enter a date range into the spreadsheet (the blue blocks on the attached spreadsheet). The factors in colum G need to be sorted by month and input into the yellow highlighted area in column B. There may be an easier way of pulling the data from the "Data" sheet but this was the best I could figure out. Overall, January (or any of the months)needs to have the January (or appropriate) factor that fits within the date range that was input but sorted properly based on the months listed in Column A.

View Replies!   View Related
Find Start Date And End Date In Range
This is a sub that uses the Find method to find a series of dates and copy them to another worksheet. The following error comes up: Object variable or With block variable not set. I have tried using a set command etc. but other errors end up coming up.

Private Sub CommandButton7_Click()

On Error Goto errorHandler
Dim startDate As String
Dim stopDate As String
Dim startRow As Integer
Dim stopRow As Integer
startDate = InputBox("Enter the Start Date: (mm/dd/yy)")
If startDate = "" Then End
stopDate = InputBox("Enter the Stop Date: (mm/dd/yy)")
If stopDate = "" Then End
'startDate = Format(startDate, "mm/??/yy")
'stopDate = Format(stopDate, "mm/??/yy")
startRow = Me.Columns("A").Find(startDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
stopRow = Me.Columns("A").Find(stopDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
Me. Range("A" & startRow & ":A" & stopRow).Copy _
Destination:=Worksheets("Report").Range("A1")
End

errorHandler:
MsgBox "There has been an error: " & Error() & Chr(13) _
& "Ending Sub.......Please try again", 48

End Sub

View Replies!   View Related
Counting Date Entries Between A Date Range
I am trying to insert a formula that counts the number of dates within a certain date range. why this sample file does not work, the answer I am looking for in cell C1 is 3.

View Replies!   View Related
Next Date Matching Criteria Within Date Range
I'm setting up a spreadsheet to manage recurring tasks that fall within a given date range.

I have a named variable (theDate) which pulls the list of matching tasks that fall on a single day, but some fall on multiple days, so I need to somehow extract the next valid date the task will come due.

I've been playing with formulas until my brain got scrambled. I suspect I may not have got enough sleep over Christmas and there's something staring me in the face I'll kick myself over.

Column Headings are:
Description (Col B), [various notes C:E], M, T, W, T, F, S, S, (Cols F:L, marked with x when relevant), StartDate (M), EndDate (N) and my nemesis, NextDate.

That formula should check that 'theDate' falls within the range, if so, lookup whether the appropriate weekday is null or not, and if it's not, then the current date comes back. If it is, then I need it to find the next weekday (by that I don't mean M-F, I mean any of the 7) when it will fall and return that date. I do have the weekday number in F2 if needed, and I'm using a weekday return type of 2 (Mon=1).

'theDate' will usually be in the future, but not always. It needs to not fall over if it's past.

I'd like to do this via formula ideally, since the SOE I'm on keeps losing my Analysis Toolpak, but if I have to code, I have to code.

View Replies!   View Related
Is Date Betwen 2 Column Date Range
I am trying to create an array formula which confirms whether a specific date falls within a list of date ranges. I have a list of start dates in column A and end dates in column B. Each row represents a date range. How can I write an array formula to check if a date falls within any of these data sets (i.e. date ranges)? I have attached a sample spreadsheet to show

View Replies!   View Related
Determining The Same Row In Different Columns
What code can i use to determine the same rows in 2 different columns and compare the data in those two cells?

View Replies!   View Related
Date Range (formula Show A Week Number In The 2nd Table )
I have two tables: the 1st table consists of date range (From and To) and week number while the other table has only dates.

Example:
1st Table
FROM TO WK
3/27/2009 4/2/200914
4/3/2009 4/9/200915
4/10/2009 4/16/200916
4/17/2009 4/23/200917
4/24/2009 4/30/200918

2nd Table
DATE
03/28/2009
04/11/2009
04/26/2009

Need simple formula that would show a wk number in the 2nd table (2nd column)? I.e 03/28/2009 has wk no. 14, etc.

View Replies!   View Related
Formula To Compare A Given Date To Todays Date
way to have the vehicle registration date compared to todays date and have it return either due next month, due or over due.

Todays date is in format month/day/year, registration is only by month/year. Todays date is in fixed location in worksheet updated by function Today()

Basically a formula that resembles =If(registration date < todays date, overdue, If( registration date = todays date, due, If(registration date = todays date+1month, next month, If( registration date > todays date + 1 month, 0))))

View Replies!   View Related
Formula To Show A Date Range Based On The Amount Shown In Another Cell
Attached is a excel file that has a working formula for tracking cashier variances. I edited out names etc.

I added a new cell called Track Back on the employee search sheet.

What I want to do is only show variances for the amount of days back selected in the Track Back cell.

For example if I select the last 30 days, only the last 30 days would show up below in the sheet.

I am not sure if this is even possible based on the forumla that is already on the sheet. I couldn't figure out a way of doing it. But there are a lot of people on here much better with excel than me

View Replies!   View Related
Formula To Copy Lines Depending On Date Range And Agent Selected
What I would like to do with the information that I have put together on a different workbook is to reference an Agent (column A), and a time range (Date is in column D) and then copy the row from A to I for each occurrence during that range of dates so we can see how many and what errors have occurred during any range of dates selected. (I will have different people inputting errors at different times so the dates will not be sorted unless they have to be). I want to be able to select different agents/date ranges to be able to get a glance of how that particular agent did during this time frame.

Here is an example of the work sheet where the data is originaly.

[Removed]

Here is an example (in a different workbook) of what the sheet should look like if I was searching errors for Albert Ozier between 1/25/2010 and 1/28/2010.

[Removed]

View Replies!   View Related
Compute The FuTure Date When Start Date,Lead Date,Weekly Offs,Leaves And Holidays
First and foremost I would like to congragulate you on this wondeful piece of code in the below link...

The query was to get a future date excluding Fridays and Holidays...

http://www.excelforum.com/excel-work...rkingdays.html

I have a similar query and therefore I pasted this link...

I actually wanted to get a future date using a Dynamic two day off as my the offs keep on changing as well as incorporate Holidays and Leaves if any..

Now Holidays would be official Public Holidays and
Leaves would be taken by the employee..

The code needs to pick the Leaves + Holidays and different offs maybe even more than 2 offs...


View Replies!   View Related
VBA To Generate Row For Each Date In Date Range
I've created a userform to track attendance. The code below creates entries based on the activities undertaken on that day; one row for each activity reflecting Name, ID, date, Activity and comments. The intent is that several activities can be undertaking on a day, and the code generates separate rows for each activity....

View Replies!   View Related
Date/Time Formula: Pick Up A Date With Time Entry On A Worksheet And Place It Into A TextBox On A UserForm
I am attempting to pick up a date with time entry on a worksheet and place it into a TextBox on a UserForm. Format on the sheet is mm/dd/yyyy h:mm AM/PM. The UserForm is placing the value as mm/dd/yyyy 12:00 AM. here is the

Private Sub UserForm_Initialize()
If Not Range("dDate").Value = "" Then
TextBox2.Value = Range("dDate").Value
TextBox2.Text = Format(DateValue(TextBox2.Text), "mm/dd/yy h:mm AM/PM")
Else
TextBox2.Value = ""
TextBox2.SetFocus
End If
End Sub


"dDate" is the named range where the date is sitting. The format is also set on the TextBox2 exit event. Can anyone see why only the date portion is being transfered with the default 12:00 AM for no time component of the value?

View Replies!   View Related
Date Minus A Date Formula
I am trying to add a formula that will give me the amount of days overdue by subtracting two dates:

example

Column D3 has 20/02/2008
Column D4 has 20/02/2009

how can I formulate a sum to give me the number of days overdue?

How can I formulate a sum to then give me the number of plus days?

View Replies!   View Related
If Date Statement Formula Look At A Date
How can I have an if statement or other type of formula look at a date and add 26 months and determine if it's 26 months old yet.

example
assuming that November 4, 2005 is one day from being 26 months
as of today That cell next to it would say "Available"
Then the next it would say "Retired" because it hit the 26 month old period.

View Replies!   View Related
Date Formula: Add A Month To A Date
i use dates a LOT in my job however if i want to add a month to a date the best i came up with is the following:

BTW all dates are USA standard (m/d/y)

A1 = 12/1/2008
Lets say i want to add 3 months but the day NEEDS to be 1 because of all of my array sumif's are based off of month 12/08, 2/09 etc, and in excel 12/08 actually translates to 12/1/08. So here is the formula I have been using for the last 4 years....

=date(year(a1),month(a1)+3,day(1)

that seems a bit "Clunky" to me anyone have a better idea?

do not tell me "=a1+30" or "31" because that does not work universally.

View Replies!   View Related
Sort Table In Date Order But With The Date Nearest To Today's Date At The Top
I have data going in to a small table which has some empty rows as that data is not yet available... My problem is, I need to sort this table in date order but with the date nearest to today's date at the top...

The sort function puts oldest at the top or oldest at the bottom which is no good for what I need...

I use xl 2003.

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved