Comparing 2 Date Ranges?

Dec 4, 2012

[URL]

take a look at the attached example and let me know how to make the formula work?

For each "title" in column A, I want to compare the two sets of start and end dates (columns B & C versus D & E) and if the date range in column B &C overlaps with the date range in column D & E, input "yes" or "no" in column F.

In my attached example, rows 8,9,11, and 12 would be populated with a "yes", the rest would be "no".

View 3 Replies


ADVERTISEMENT

Comparing The Ranges

Sep 25, 2008

I have 3 sheets. Sheets 1 & 2 contain lists of data occupying columns A to M. Although the data on these sheets generally will be different, there are occasions where sheet 2 may contain identical rows to sheet 1.

What I want to do is two separate things -

copy the duplicated rows to sheet 3, and then in columns N & O indicate the row numbers from sheets 1 & 2 where the duplicates can be found (this only where the entire row matches)

do the same as above, but only where the contents of columns I & J match (rather than the entire row).

View 9 Replies View Related

Summing And Comparing 2 Ranges

Feb 11, 2009

I'm on Vista with Excel 2007, HOWEVER, the solution must be Excel 97-2003 compatible.

I have two columns. One will a Y or N in it, the other will have a number. I need to sum the column with numbers, but I ONLY want the numbers added that have a 'Y' in the same row of the other column ...

View 6 Replies View Related

Comparing Numbers To A Variety Or Ranges?

Jul 7, 2014

I have a list of numbers which I need to compare to a range of numbers. Each number in the list needs to be compared to the range, and depending where it falls within the range, a particular cell needs to be referenced in the result. I've attached the xls with the explanation.

I was going to do this with a nested IF, but with a max of 7 this will not work.

Attached File : Help with comparing to a range.xlsx‎

View 5 Replies View Related

Comparing Two Ranges And Returning A Match

Jan 22, 2010

I need a function to compare two ranges and return a match. For example

Range A: {A56, B43, C98, D44}
Range B: {A33, G89, D44, K123, OB55}

the function would return D44. Is it possible at all with functions or do I need a macro?

View 14 Replies View Related

Comparing Multiple Ranges With Conditional Logic?

Apr 23, 2012

[1] compare (row) ranges across two columns with an unequal number of rows (column A [number] to column C [number])
[2] save each result of [1] where there was a match in column B
[3] for each row where there was a match (now stored in B), compare the value from the same row in column D (date) with the same row of column F (date), and store the result in column E
[4] count and message box the final number of matches from column E

Data Example:

A B C D E F
----- ----- ----- ----- ----- -----
12345 match 12345 11/1/2011 match 11/1/2011
77777 34345 ... ...

A and C numbers match, so check same row date in D that does match date in E, place "match" in E, then count and display.

Sub other()
Dim range1, range2, range3, range4, range5, range6 As range
Dim x As Variant, y As Variant, z As Variant, a As Variant

[Code].....

View 1 Replies View Related

Return Matching Numbers By Comparing 2 Ranges

Apr 30, 2008

I have a collum (A )with numbers and in other sheet have a collum (B)with numbers too.. and some numbers match. what i want is in the cells that the numbers match have other colum and i want to copy that number to another cell.

View 2 Replies View Related

Comparing Previously Entered Date With New Date In Same Cell?

Apr 15, 2014

I'm working on the final stage of a project. I'm attempting to write code that will set off a chain of events if two dates are 91+ days apart from each other . I've attached a sample worksheet that shows the bare bones basics of what I'm attempting to do.

What will be the most efficient and effective way of accomplishing my goal: userform, functions, formatting, etc.? T

View 14 Replies View Related

VBA Comparing Date In Cell To Today's Date

May 12, 2009

I need to VBA code that will loop through the active sheet for all rows, column C2 through however many rows there are. Col C contains a date in the format: mm/dd/yyyy. I need the VBA to compare this date to today's date+30 days and if the date in C2, etc. is greater than today's date + 30 days, shade the row a particular background color (e.g., 3).

View 9 Replies View Related

Date Range: Reference A Single Date And Output Date Ranges

Oct 11, 2008

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 2 Replies View Related

Comparing Date/Time

Jun 19, 2006

I'm trying to write a macro that compares 2 coloms(open,close) of dates/times and calculates how long each was open for. This is what I have so far. I've also include as an attachment an example of the date/time format i'm using.

Sub Time()
Dim A, B, C As Long

Sheets("Sheet1").Select

For i = 7 To 5000
A = Fix( Range("A" & i).Value)
B = Fix(Range("B" & i).Value)
C = A - B
Cells(C, i).Value = C

Next
End Sub

View 9 Replies View Related

Date Formatting - Comparing Dates

Jul 8, 2013

I have a problem regarding month formatting

I have 12 columns, Jan 2013 - Dec 2013.

I need to set a format in which if I enter a num eg 15 on the Jan Column, format comes as "15-Jan" format
or If i type 20 on the march column , it comes as "20-Mar" format

I would need it in Date format, because I would be comparing dates later on.

View 2 Replies View Related

Date Between Multiple Non Contiguous Date Ranges On Another Sheet?

Nov 8, 2012

I have been looking at a post number 170404 which NBVC answered with an example grid. This was while I was trying to bring searched data through to another sheet and this is just what I was looking for as a brilliant start,

I have attached my very similar example in my scenario. I have also got NBVC's example on the first two sheets of this as reference. In addition I am also bringing through from-to date ranges which I then need to be applied to a Booking Sheet so I can see that these dates are booked for this person, and ideally where they are going and what they need to do there by colour.

I tried, just as a test in this example the following. Please note that this only had the three ranges as I was testing an example coordinator which returned three date ranges. It could be that the example returns 20 or 30 ranges that need to be applied to the rather primative Booking Sheet.

=IF(OR(AND(C10>='Coordinators list'!F5,'Coordinators list'!L5<=C10),(AND(C10>='Coordinators list'!F6,'Coordinators list'!L6<=C10)),(AND(C10>='Coordinators list'!F7,'Coordinators list'!L7<=C10))),"yes","no")

It seemed to give the right answer initially but trying different dates it gave the wrong result. It seemed to be checking one of the ranges and not all of them. As mentioned above this was just an example to see what I could get working but I don't think that this would be the ideal way of doing this as it would need to check 30-40 maybe more ranges depending on what comes back from the coordinator search.

The aim would be to choose a co-ordinator on the Site Planner sheet and ideally return the city rather than the yes/no in the Booking Sheet for that date and use conditional formatting so that if the type from the Site Planner Sheet was paint it would fill in one colour, clean in another, etc.

Was hopefully trying to get this done without using any VB as I am trying to resolve this for my Wife she isn't very tech savvy and and I need to be able to explain the formulas!

I'm sure you will see from my sheet I have given this a good go but I am a bit unsure on what some of the formulas on NBVC sheet are doing, and that this is my first attempt at doing anything like this. I have also tried using lookup and match but I'm still having issues that they either aren't always in date order and it seems to return no if it doesn't match the first range. The other main issue that I have seen is that I need to check whatever ranges the selection comes back with rather than adding each one to search through.

I hope this becomes a bit clearer on the sheet I have attached,

View 9 Replies View Related

Using VLookup To Lookup Date Within Multiple Date Ranges

Jan 5, 2012

I have a table with three columns. I'm building a calendar on a separate worksheet and am looking up the "value" based on a calendar date. So if a date falls within any of the ranges, I'd like to return the value in column C. For example, if the date is 02/07/12, I'd like for the result to be value 1, or if the date is 04/17/12, then I would like the result to be value 3. I've used a nested vlookup, but all that give me is the value when either the start or end dates match, but I can't get a value when the date falls within the range. If the dates were consecutive, I would simply use vlookup/TRUE, but the dates are not consecutive.

ABC102/06/1202/09/12value 1203/12/1203/15/12value 2304/16/1204/19/12value 3405/21/1205/24/12value 4506/25/1206/28/12value 5606/25/1206/28/12value 6

View 4 Replies View Related

Finding Data That Has 2 Work Orders And Comparing With Completion Date

May 31, 2007

What I am looking for is a macro/formula that will look at a unit number, then the type of work done, then the completion date and decide if the same type of work was done on the same unit within a 7 day span of time.

Example

Unit is 11111, work is plumbing completion date is 10/4/07
Unit is 11111, work is plumbing completion date is 10/7/07

This means the work has been done 2 times (obviously showing that it did not get fixed the first time)

Is there any way to mark an X in the square with the formula, or any type of identifier what-so-ever

I have attached a spreadsheet and hope it helps

Again I know this is complex so it is OK if no one feels like messing with it but I wanted to check here first.

View 3 Replies View Related

Compare Date Within Date Ranges

Mar 1, 2007

I want to have a new macro/button to redo the chart cells based on the dates in cols T and U (abt 400 entries). T has the start date and U has the end date.

Row 9 - X9: CJ9 have dates for about 10 months (weekdays only)
Column J have either of these two values: A or B
Column C has either of these two values: C or blank

I would envisage it going through each row, checking:

If date at top of that col was between the dates in cols T & U
then
if col J = 'A' then set cell to black block
else if col J = 'B'
then
if col C = C (closed) then set cell to red block
else set cell to blue block

View 2 Replies View Related

Sum Based On Date Ranges?

Dec 13, 2012

Sum based on date ranges. I am creating one logical formula.

I have an excel sheet with two columns.

A1: Date B1: Time Spent

Date
Time spent (in Minutes)
Total

[Code].....

As per above figure I want to sum based on the same dates. I.e A2 has date 1/26/2012 for which first entry is 12 so total will say 12 in C1, for the same date second entry is 14 so 12+14=26 is there in total at C2, for the third and last entry of 1/26/2012 it's 16 so total would be 12+14+16 = 42 in C3 which is grand total for 1/26/2012. I want to fill entire C column with some logical formula so that the logic holds true for various dates.

I have tried to use the IF formula like =IF(A1=A2,SUM(B1:B2)) but it fails at third cell. I have tried several ways to do this but no luck so far ....

View 2 Replies View Related

Formula Looking At Date Ranges?

Oct 28, 2008

I have start dates (column A) and maintenance expiry dates (column B). What I want to do is set up a formula to see if the product was renewed between 01/04/2007 - 31/03/2008 and return a simple yes, no or new_sales.

Examples

If the start date was 26/09/2006 and maintenance expiry date was 26/09/2008 then it would return a yes that they have renewed

If the start date was 18/04/2006 and maintenance expiry date was 18/04/2007 then it would return a no that they have not renewed

If the start date was 20/06/2008 and maintenance expiry date was 20/06/2009 then it would return a new_sale

View 12 Replies View Related

Return A Value Between Date Ranges

May 25, 2006

I have one worksheet with 52 rows for each week of the year with the
following headings: week #, start date of week and end date of week. I have
another worksheet where I need to forecast expected expenses for new
employees based on the date they start ie. Joe starts in Feb 6 (week 6) and
then calculate expected spend based on remaining weeks in the year. I do
this manually right now. How can I automate this?

View 10 Replies View Related

Date Ranges Intersection

Nov 24, 2008

There is a database with work plans for a project cycle, which contains employee names, types of activities, planned working hours for each activity (not in time format, just number) and dates when these hours will take place. Each employee may have one or multiple activities and each activity has different number of hours planned to complete and different dates. My aim is to calculate the number of hours planned for each employee (to sum all activities) within a certain period of time.

So, what is the question? I need to calculate the number of workdays within the intersection of the planned period for the activity and my given period of time.

I'll give a small example to illustrate what I mean.

I'm calculating planned working hours for this week, which is 11/23/2008-11/29/2008. One of our employees, let's call him John, is going to work on three activities, A, B, and C. We have the following plans:

Activity A - 16 hours - 11/24/2008-11/27/2008
Activity B - 45 hours - 11/15/2008-11/25/2008
Activity C - 10 hours - 11/26/2008-12/01/2008

As you can see, activity A is going to be complete within this week, it's perfectly fine. Activities B and C should be counted separately, because they include days earlier or later than this week. Is there any way I can easily geet the number of workdays that are included in the plan period and this week?

Another thing to mention is that I'm working in Excel 2003 and it's not acceptable for me to install the Tools Pack, because I need this firmula to work on other employees computers and there is no chance they all will install it too.

View 14 Replies View Related

Determining Date Ranges

Mar 31, 2009

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 4 Replies View Related

Average Between Date Ranges

Jun 8, 2009

I would like to get a monthly average between the first of the month & the end of month of a handful of percentages collected during the month for individual projects.

View 10 Replies View Related

SUMIFS In VBA With Date Ranges?

Sep 17, 2012

I'm trying to recreate a formula in VBA, but struggling with the "greater than /equal to start date" and "less than / equal to end date" parts

this formula works ok in the spreadsheet:

Code:
=SUMIFS(bbHours,empCodeList,thisEmployeeID,bbDate,">="&startDate,bbDate,"=" & startDate, Range("bbDate"), "=" section, but unclear how to handle this. It must be handled entirely via VBA, not formulas

Note: I've adjusted certain key names / VBA strings for clarity, everything is defined correctly

View 1 Replies View Related

Sum For Varying Date Ranges

Jul 2, 2008

What I'm trying to do is sum the daily interest of a loan for any range of dates. Imagine this example:

1) In column A you have dates from 01/01/2009 through 12/31/2059, with each individual day showing, so literally thousands of cells.

2) In column B you have just random values corresponding to each date, such as $100,000 on 01/01/2009, $139,000 on 01/02/2009, $52 on 01/03/2009, and so forth. These values occupy as many cells as the dates, i.e. there's a cash flow (value) for every date.

3) I want to run a formula in column C that will sum a date range I specify. For example, if I want to sum all the daily values for each month (i.e. the 1st through the 30th), or if I want to sum all the daily values for the 15th of a month through the 14th of the following month. In other words, the loan can start on any date, and I need to sum the interest for each "monthly anniversary."

4) I'm thinking that the formula will have to be different to sum an actual entire month. For example, formulas to sum from, say, the 15th of one month through the 14th of the next month, or the 10th of one month through the 9th of the next month, might be similar.

However, as there is a varying number of days in most months, that formula might not work (though I might be misunderstanding myself) when trying to sum the values for an entire month, i.e. February 1st through 28th. I think it might be solved by some sort of "if," such as if day = 1, then sum whole month, otherwise some from date through same date of next month less the last date's cash flows?

View 9 Replies View Related

Using Date Ranges In Formulas ...

Feb 18, 2009

I am having trouble entering a formula that measures how many times "Open" occurs in one column IF another column's entry is between 2 dates or within one particular month time frame. I've tried a bunch of formulas but can't seem to get anything to work!

View 9 Replies View Related

DSUM Date Ranges

Apr 14, 2009

I an having trouble with DSUM, the result I get is 0. I have a Journal Entry and I need to total the amount for a particular range date.

For the header I have:
Date|Account|Debit|Credit|Total|Account Name|Description

This is located from A5:G21

For A1:C2 I have Account|Date Start|Date End
For both Date I used the formula =">="&TEXT(E2,"mm/dd/yyyy") and the same for the ending date which is ="

View 9 Replies View Related

COUNTIF For Date Ranges

Oct 27, 2009

i had some formula provided by this site which seemed to work but is now resulting in #N/A or a zero value - can you take a look?

=SUMPRODUCT(('Report X'!$J$6:$J$10000="2")*('Report X'!$E$6:$E$10000>TODAY()-30))

Report X contains 2 relevant columns... E contains the date created for each item, J contains the type of each item - in this case i wish to see how many items '2' there are in the last 30 days

Report X is a daily report copied into the same tab each day - so the data will change every day depending how long each item is still in the system

this formula does work in the example sheet i provided but not in the spreadsheet in which i need it to work!

i also have similar formulas to calculate how many items are present in between 30 and 60 days exist, and how many over 60 days - all return the value "0"

column E containing dates is in the format 01/10/2008 - changed using Ctrl F - replace to amend it from 01.10.2008

View 9 Replies View Related

Sum & Report By Date Ranges

Sep 19, 2007

in column A, i have a dates listed, in column G, the transaction amounts that correspond with those dates, i want to know how to run a report on this. what i need is to auto sum the amounts for a specific month without having to create a new spreadsheet for every month. IE - what are the sales for september? how do i do this?

View 2 Replies View Related

Counting Blanks In Date Ranges

May 19, 2013

I am trying to work on a spreadsheet (I am very new to all this). I have colum I with dates in and Colum K with dates in... When a product comes into my shop I enter the date it arrived in Colum I and when it leaves I enter the date in Colum K.

What I would like to try to work out is how do I ask Excel to tell me How many blank cells there are in Colum K if I enter a date range for Colum I

I will try to give an example.

Colum K may have 200 entries for the week 6/5/13 to 10/5/13. I need to know how many cells are blank in Colum K if the date range on Colum I is between 6/5/13 to 10/5/13

The end result should basically tell me how much stock I have left in my shop for that week.

I have found something that sort of works by using this =SUMPRODUCT((I1:I200<=TODAY())*(K1:K200="")) but ideally I need to be able to change the word TODAY into a date range like 6/5/13 to 10/5/13 and I cant seem to do that???

View 5 Replies View Related

Indirect Functions And Date Ranges?

Dec 9, 2013

I have an INDIRECT function set up which should, in theory, concatenate data from a date range listed in another cell to correctly reference an external spreadsheet, which is named based on the date range it is generated for. The issue I'm running into is that the program which generates the external spreadsheet uses an MMDDYYYY format when naming its output file. This works fine if the month and day are both 10 or greater, but if they are less than 10, it doesn't work - for example, my INDIRECT function would return the date 5/3/13 as 532013, whereas the file name would be 05032013. I was contemplating using a series of IF statements to fix this, but there has to be a simpler way. Here are the relevant functions.

Function which generates date range (later used in INDIRECT function):

=MONTH(MIN(TransData!A2:A9882))&"/"&DAY(MIN(TransData!A2:A9882))&"/"&YEAR(MIN(TransData!A2:A9882))&" - "&MONTH(MAX(TransData!A2:A9882))&"/"&DAY(MAX(TransData!A2:A9882))&"/"&YEAR(MAX(TransData!A2:A9882))

TransData is a different sheet within the same workbook where the various dates are stored in column A. I found that I needed to format the formula like this in order to get it to return actual dates instead of date sequence numbers.

=AVERAGE(INDIRECT("'[DCQA_"&SUBSTITUTE(B4,"/","")&".xlsm]Total'!$C$3:$C$46"))

Cell B4 is where the date range formula listed above lives. "DCQA_" is the beginning of the filename of the external sheet, followed by the date range in MMDDYYYY - MMDDYYYY format. So, the sheet for 12/2/13 to 12/7/13 would be named DCQA_12022013 - 12072013. I'm unfortunately unable to change the way the external program names its output sheets, so I have to fix this from within my workbook.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved