Formula for matching a date within a date range
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell C9 that uses the date specified in cell C8 to match a date range.
A date range is matched if the date is larger or equal to the start date and smaller or equal to the end date. The date ranges are in cell range C3:D6.
The date in cell C8 matches date range 4-1-2022 / 6-30-2022, the formula returns a value on the same row from cell range B3:B6, in this case, Item "B".
Table of contents
- Match a date when a date range is entered in a single cell
- Match a date when a date range is entered in two cells sorted in ascending order
- Match a date when a date range is entered in two cells with gaps not sorted
- Use VLOOKUP to search date in date ranges and return value on the same row
- Match a date when date ranges sometimes overlap and return multiple results
1. Match a date when a date range is entered in a single cell
Column C contains the start and end date separated by a forward slash /. The formula in cell C9 splits the dates and checks if the date in cell C8 is in a date range and if it is it returns the adjacent value on the same row.
Formula in C9:
1.1 Watch a video where I explain the formula above
1.2 How to create an array formula
- Select cell C9.
- Press with left mouse button on in the formula bar.
- Copy and paste array formula to the formula bar.
- Press and hold CTRL + SHIFT simultaneously.
- Press Enter.
- Release all keys.
1.3 Explaining formula
Step 1 - Calculate character position of forward slash
The FIND function returns the position of a specific string in another string, reading left to right. Note, the FIND function is case-sensitive.
FIND(find_text,within_text, [start_num])
FIND("/", C3:C6)
returns {11; 11; 11; 11}.
Step 2 - Calculate the number of characters in each cell
The LEN function returns the number of characters in a cell value.
LEN(text)
LEN(C3:C6)
returns {21; 21; 21; 21}.
Step 3 - Calculate position from last character
The minus character lets you perform an arithmetic operation, it subtracts one value with another value.
LEN(C3:C6)-FIND("/", C3:C6)
returns {10; 10; 10; 10}.
Step 4 - Extract characters
The RIGHT function extracts a specific number of characters always starting from the right.
RIGHT(text,[num_chars])
RIGHT(C3:C6, LEN(C3:C6)-FIND("/", C3:C6))
returns
{"03-31-2009"; "06-30-2009"; "09-30-2009"; "12-31-2009"}
Step 5 - Convert text to date
The DATEVALUE function returns an Excel date value (serial number) based on a date stored as text.
DATEVALUE(date_text)
DATEVALUE(RIGHT(C3:C6, LEN(C3:C6)-FIND("/", C3:C6)))
returns {39903; 39994; 40086; 40178}.
Step 6 - Check if larger than or equal to
The larger than and equal signs are logical operators, they are used in this example to check if a date is in a date range.
The output is a boolean value TRUE or FALSE, the position of each value in the array correspond to the date ranges.
DATEVALUE(RIGHT(C3:C6, LEN(C3:C6)-FIND("/", C3:C6)))>=C8
returns
{TRUE; TRUE; TRUE; FALSE}.
Step 7 - Check date against end dates
DATEVALUE(RIGHT(C3:C6, LEN(C3:C6)-FIND("/", C3:C6)))>=C8
returns
{FALSE; FALSE; TRUE; TRUE}.
Step 8 - Create number sequence
The ROW function calculates the row number of a cell reference.
ROW(reference)
ROW($C$3:$C$6)
returns
{3; 4; 5; 6}.
Step 9 - Create a number sequence from 1 to n
The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.
MATCH(lookup_value, lookup_array, [match_type])
MATCH(ROW($C$3:$C$6), ROW($C$3:$C$6))
returns
{1; 2; 3; 4}.
Step 10 - Multiply arrays
The asterisk charcater lets you multiply arrays.
(DATEVALUE(LEFT(C3:C6, FIND("/", C3:C6)-1))<=C8)*(DATEVALUE(RIGHT(C3:C6, LEN(C3:C6)-FIND("/", C3:C6)))>=C8), ROW(C3:C6)-MIN(ROW(C3:C6))+1)
returns
{0; 0; 3; 0}.
Step 11 - Sum numbers
The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.
SUMPRODUCT((DATEVALUE(LEFT(C3:C6, FIND("/", C3:C6)-1))<=C8)*(DATEVALUE(RIGHT(C3:C6, LEN(C3:C6)-FIND("/", C3:C6)))>=C8), ROW(C3:C6)-MIN(ROW(C3:C6))+1))
returns 3.
Step 12 - Get value
The INDEX function returns a value from a cell range, you specify which value based on a row and column number.
INDEX(array, [row_num], [column_num])
INDEX($B$3:$B$6, SUMPRODUCT(DATEVALUE(RIGHT(C3:C6, LEN(C3:C6)-FIND("/", C3:C6)))>=C8)*(DATEVALUE(LEFT(C3:C6, FIND("/", C3:C6)-1))<=C8), MATCH(ROW($C$3:$C$6), ROW($C$3:$C$6))))
becomes
INDEX($B$3:$B$6, 3)
and returns "C" in cell C9.
1.4 Exel file, see sheet Ex 1
matching-a-date-in-a-date-range v3.xlsx
(Excel 2007- Workbook *.xlsx)
2. Match a date when a date range is entered in two cells
There are no gaps between these date ranges which makes it is possible to only use the dates specified in column C in the following formula:
Formula in cell C9:
Note, there are no gaps between date ranges. If you have gaps use the formula in section 3.
2.1 Watch a video where I explain the formula above
2.2 Explaining formula
Step 1 - LOOKUP function
The LOOKUP function lets you find a value in a cell range and return a corresponding value on the same row.
LOOKUP(lookup_value, lookup_vector, [result_vector])
The values in lookup_vector must be sorted in ascending order or from A to Z.
Step 2 - Populate arguments
LOOKUP(C8,C3:C6,B3:B6)
lookup_value - The lookup value is in cell C9.
lookup_vector - The lookup cell range is C3:C6. Note start dates are sorted in ascending order.
[result_vector] - The function returns a value from this range on the same row as the matching lookup value.
Step 3 - Evaluate LOOKUP function
LOOKUP(C8,C3:C6,B3:B6)
becomes
LOOKUP(39994, {39814; 39904; 39995; 40087}, {"A"; "B"; "C"; "D"})
and returns "B".
3. Match a date when a date range is entered in two cells with gaps not sorted
Remember, the LOOKUP function requires date values to be sorted in an ascending order in column C. If not, use the formula below.
If you do have gaps between some date ranges, like the picture below. There is a date gap between 3-1-2009 and 4-1-2009. You then need to use both the start and end date to find the value you are looking for.
Formula in cell C9:
If the user enters a value outside the date ranges error #N/A is returned.
3.1 Watch a video where I explain the formula above
3.2 Explaining formula in cell C9
Step 1 - Check if the end dates is larger or equal to date
The less than, larger than, and equal signs are all logical operators. They return a boolean value True or False.
$C$8>=$C$3:$C$6
returns
{TRUE; TRUE; FALSE; FALSE}.
Step 2 - Check if the start dates is smaller or equal to date
$C$8<=$D$3:$D$6
returns
{FALSE; TRUE; TRUE; TRUE}.
Step 3 - Multiply arrays - AND logic
The parentheses let you control the order of calculation. The asterisk multiples two numbers or two arrays.
($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6)
returns
{0; 1; 0; 0}
Step 4 - Create a number sequence
The ROW function calculates the row number of a cell reference.
ROW(reference)
ROW($B$3:$B$6)
returns {3; 4; 5; 6}.
Step 5 - Create a number sequence from 1 to n
The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.
MATCH(lookup_value, lookup_array, [match_type])
MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6))
returns {1; 2; 3; 4}.
Step 6 - Multiply with relative row numbers
($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6)* MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6))
returns {0; 2; 0; 0}.
Step 7 - Sum values in the array
The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication. It also has the advantage that you don't need to enter the formula as an array formula.
SUMPRODUCT(($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6)* MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6)))
becomes
SUMPRODUCT({0; 2; 0; 0})
and returns 2.
Step 8 - Return value from cell range
The INDEX function returns a value from a cell range, you specify which value based on a row and column number.
INDEX(array, [row_num], [column_num])
INDEX($B$3:$B$6, SUMPRODUCT(($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6)* MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6))))
returns "B" in cell C9. Value "B" is the second value in the array.
4. Use VLOOKUP to search date in date ranges and return value on the same row
The following formula uses only the VLOOKUP function, however, the dates must be sorted in ascending order and if a date is outside a date ranges 0 (zero) is returned. There can't be any overlapping date ranges and the formula can only return one value.
The example demonstrated in the image above has date ranges in only one column. Every other date is the end date of each date range.
You are also required to have the lookup column in the first column in the cell reference you use in the VLOOKUP function. Example, the second argument in the VLOOKUP function below is this cell reference: B3:C10. The lookup column must be in column B.
Formula in cell C13:
Important! The first date range seems to be 1-1-2009/3-31-2009 but it is actually 1-1-2009/3-30-2009, why is this? The VLOOKUP function matches the largest date that is smaller or equal to the lookup date. If the lookup date is 3-31-2009 it will match 3-31-2009 found in cell B4 and return the corresponding value in column C (cell C4). In this case nothing, cell C4 is empty. This applies to all date ranges in column B.
You need to change your date ranges accordingly if you want to use the VLOOKUP function for date ranges entered vertically. However, the VLOOKUP function works perfectly fine if you have date ranges with no gaps between the end dates and start dates, see picture below. You then only need to use the start dates for each date range, example demonstrated in column C see picture below.
Recommended article
Recommended articles
In this article, I will demonstrate four different formulas that allow you to lookup a value that is to be found […]
4.1 Explaining formula
VLOOKUP(C12,B3:C10,2,TRUE)
Step 1 - VLOOKUP function
The VLOOKUP function lets you search the leftmost column for a value and return another value on the same row in a column you specify.
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
The values in lookup_vector must be sorted in ascending order or from smallest to largest
Step 2 - Populate arguments
VLOOKUP(C12, B3:C10, 2, TRUE)
lookup_value - Date value specified in cell C12
table_array - A cell range containing both the lookup column and the return value column,in this case, B3:C10.
col_index_num - From which column to return a value from.
[range_lookup] - True or False (boolean value). True - approximate match, the leftmost column must be sorted in ascending order, or from small to large. False - Exact match.
Step 3 - Evaluate VLOOKUP function
VLOOKUP(C12, B3:C10, 2, TRUE)
returns "B" in cell C13.
4.2 Get Excel file, see sheet Ex 4
matching-a-date-in-a-date-range v3.xlsx
(Excel 2007- Workbook *.xlsx)
5. Match a date when date ranges sometimes overlap and return multiple results
This example demonstrates a formula that returns multiple values if a date condition is met in multiple date ranges. This is only possible if the date ranges overlap.
For example, date 7/2/2009 above matches both date ranges 4/1/2009 - 14/7/2009 and 7/1/2009 - 9/30/2009. The corresponding values to those date ranges are "B" and "C".
Array formula in cell C9:
How to copy an array formula
- Select cell C9.
- Copy cell (not formula). (shortcut keys CTRL + c)
- Select cell range C10:C11.
- Paste (shortcut keys CTRL + v).
5.1 Explaining formula
Step 1 - Check if the end dates are larger or equal to the date condition
The less than, larger than, and equal signs are all logical operators. They return a boolean value True or False.
The following expression is the first logical test to check which date range a date matches.
$C$8>=$C$3:$C$6
returns {TRUE; TRUE; TRUE; FALSE}.
Step 2 - Check if the start dates are smaller or equal to the date condition
$C$8<=$D$3:$D$6
returns {FALSE; TRUE; TRUE; TRUE}.
Step 3 - Multiply arrays - AND logic
The parentheses let you control the order of calculation. The asterisk multiples two numbers or two arrays.
TRUE * TRUE = TRUE (1)
TRUE * FALSE = FALSE (0)
FALSE * TRUE = FALSE (0)
FALSE * FALSE = FALSE (0)
The numerical eqivalents to TRUE is 1 and FALSE is 0 (zero)
($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6)
returns {0; 1; 1; 0}.
Step 4 - Create a number sequence based on rows in the cell reference
The ROW function calculates the row number of a cell reference.
ROW(reference)
ROW($B$3:$B$6)
returns {3; 4; 5; 6}.
Step 5 - Create number sequence from 1 to n
The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.
MATCH(lookup_value, lookup_array, [match_type])
MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6))
returns {1; 2; 3; 4}
Step 6 - Filter row numbers based on critera
The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.
IF(logical_test, [value_if_true], [value_if_false])
IF(($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6), MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6)))
returns {FALSE; 2; 3; FALSE}.
Step 7 - Extract k-th smallest row number
The SMALL function returns the k-th smallest value from a group of numbers.
SMALL(array, k)
SMALL(IF(($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6), MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6))), ROW(A1))
returns 2.
Step 8 - Get value from cell range
The INDEX function returns a value from a cell range, you specify which value based on a row and column number.
INDEX(array, [row_num], [column_num])
INDEX($B$3:$B$6, SMALL(IF(($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6), MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6))), ROW(A1)))
returns "B".
5.2 Get excel file, see sheet Ex 3
matching-a-date-in-a-date-range v3.xlsx
(Excel 2007- Workbook *.xlsx)
Dates category
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
Dates basic formulas category
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
Excel categories
57 Responses to “Formula for matching a date within a date range”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
This much shorter formula appears to work also...
=LOOKUP(C8,RIGHT(C$3:C$6,10)-91+(C8=--(YEAR(C8)&"-03-31")),B$3:B$6)
Very interesting! Thanks!
Hi,
What type of formula could be used if you weren't using a date range and your data was not concatenated?
ie: Input Value 1.78 should return a Value of B as it is between the values in Range1 and Range2
Range1 Range2 Value
1.33 1.66 A
1.67 1.99 B
2.00 2.33 C
MT,
see this post: https://www.get-digital-help.com/2010/01/13/return-value-if-in-range-in-excel/
Hi!!
This is Ramki. Require your help to fix my concern.
Need a formula to return multiple values while using a double look up formula (Index/Match).
Hi! my date range is set up in two columns
A B C
Start_Date End_Date Item
1-1-12 2-1-12 Text_1
2-2-12 2-15-12 Text_2
I have another list where dates are in sequence and want to lookup text value for each day
A B
Date Item_result
1-1-12 Text_1
1-2-12 Text_1
Ahmed Ali,
I added new content to this post. See:
Match a date when a date range is entered in two cells
Can't thank you enough will give it a try
Again can't thank you enough it worked like a charm. the only thing is that when i search for a date that does not have a task it returns the last task. how can i avoid that. thanks
Hi,
Regarding the formula for "Match a date when date ranges sometimes overlap and return multiple results", is there a way to revise the formula to just look at the dates and indicate if there is crossover?
For example, if I have start date and end dates in four columns and start date in B1 is 1/1/10 and end date in B2 is 12/31/14 and start date in D2 is 10/1/13 and end date in E2 is 10/31/13, I'd like the formula to recognize that the 10/1/13-10/31/13 date range crosses over within the 1/1/10-12/31/14 date range.
Is this possible?
Thanks!
Brett,
I think this post is what you are looking for:
Find overlapping date ranges in excel
Thanks Oscar!
Oscar,
in 2010 excel this isnt working? im getting a #value! error?
I saved your worksheet and I press with left mouse button on c9 and hit enter and it returns the error, any idea what's going on?
anon,
Create an array formula! Instructions above!
Hi Oscar,
I need a fomula that gives me the number of days contained in a range that overlap anoter range... not sure if that is clear enough...
Rene,
read this post: Days contained in a range that overlap another range
[...] Rene asks: [...]
SIR, I AM TRYING TO FIND THE TOTAL OF QUANTITIES DURING GIVEN DATE RANGE IN EXCEL SHEET. SUPPOSE THE QUANTITIES ON EACH OF THE DAY FROM 1 TO 30 IS GIVEN, I NEED TO FIND SUM OF QUANTITIES DURING 7 TO 13. PLEASE HELP. THANKS. SRINIVAS
srinivas,
Hi! my date range is set up in two columns as ALI has
A B C
Start_Date End_Date Item
1-1-12 2-1-12 Text_1
2-2-12 2-15-12 Text_2
I have another list where dates are in sequence and want to lookup text value for each day
A B
Date Item_result
1-1-12 Text_1
1-2-12 Text_1
but if i copy formula into rows in B columns it dost work. thnx
Jan,
try this array formula:
Dear sir,
how do I use sum if to show for example to extract those quantity that match a date range like from 1.1.12 to 31.12.12
1.1.12 80 qty 80
1.11.11 50 0 (as this does fall within date range)
david,
Did you read my answer to srinivas?
https://www.get-digital-help.com/formula-for-matching-a-date-within-a-date-range-in-excel/#comment-54360
Dear Sir,
I am not sure what I am doing wrong, the following is my data and formula I am using, I initially copy one of your examples and change the value field from A,B,C, ETC to 300,320,340, etc tried diff format (gen, num)added rows and keep getting #VALUE! error, made sure fields have same format as the example but once I added the additional rows I get the error, let me know if you can help.. your help is highly appreciated. Grace
=INDEX($B$3:$B$15,MIN(IF((DATEVALUE(RIGHT(C3:C15,LEN(C3:C15)-FIND("/",C3:C15)))>=C17)*(DATEVALUE(LEFT(C3:C15,FIND("/",C3:C15)-1))<=C17),MATCH(ROW($C$3:$C$15),ROW($C$3:$C$15)),"")))
B C
Value Date range
3 300 2013-07-01/2014-06-30
4 320 2012-07-01/2013-06-30
5 340 2011-07-01/2012-06-30
6 360 2010-07-01/2011-06-30
7 380 2009-07-01/2010-06-30
8 400 2008-07-01/2009-06-30
9 420 2007-07-01/2008-06-30
10 440 2006-07-01/2007-06-30
11 460 2005-07-01/2006-06-30
12 480 2004-07-01/2005-06-30
13 500 2003-07-01/2004-06-30
14 520 2002-07-01/2003-06-30
15 540 2001-07-01/2002-06-30
16
17 Date value: 7/1/2010
18 Search result: #VALUE!
Grace Langford,
You forgot to enter it as an array formula.
Get the Excel *.xlsx file
Grace-Langford.xlsx
Many thanks, Oscar. I'm tired and cross-eyed and your formula saved me a lot of time. Worked beautifully!
Hello Oscar!
My situation is similar, but not identical to your examples:
Sheet 1 provides a date range in two cells:
1.2013 2.2013
2.2013 3.2013...
Sheet 2 provides specific dates and values
3.1.2013 500$
5.2.2013 700$...
What I want to do now is insert the Values from Sheet 2 next to the right date ranges in Sheet 1. Please note that there is only one date (and thus one value) for each date ranch.
1.2013 2.2013 500$
2.2013 3.2013 700$
Usually I would use Vlookup and match the dates, but that doesn't work since I have a date range.
I would be very thankful for your help. This problem seems so easy but I can't seem to figure out a solution!
Thanks and best regards!
Hi Oscar,
Based on the staff's joining date, the excel file should display the Percentage entitlement for that staff
Description Grade %
Joined after 01.07.2013 to 31.12.2014 8 % Nil
Joined between 01.07.2012 and 30.06.2013 8 % 5
Joined between 01.07.2011 and 30.06.2012 8 % 8.33
Joined between 01.07.2010 and 30.06.2011 8 % 16.66
Joined between 01.07.2003 to 01.07.2010 8 % 20
joined between 01.07.2003 to 30.06.2013 7 % 25
Pls. could U assist?
Rgds
Hi Oscar
I am new to your site and to posting.
I am in need of some help.
I have 2 data sets. First data set contains 3 columns of data: 1)vehicle unit#, a driver ID# and the date/time of a transaction.
The problem is that often this dataset has a null value for the tractor#, so I need to find out what tractor this driver had during the time the transaction occurred.
I have a second file obtained from OBC (On-board Computer) data that contains Login times and logout times as well as the driver#, Tractor#,Login Date/Time, Logout Time.
There can be many drivers in file 2 that are logged in with a date range that could include transaction date from file 1, so I need to be able to find not only the date within a range, but specific to the driverID.
IE: File 1
DriverId Tractor# Transaction Date
MORT 02/02/2015 04:34:00
MORT 02/02/2015 18:04:00
MORT 02/02/2015 18:59:00
JOHA 02/02/2015 03:35:00
LITMAR 02/02/2015 03:46:00
RODB 02/02/2015 10:15:00
Sorry - Sent previous post without finishing:
IE: File 1
DriverId Tractor# Transaction Date
MORT 02/02/2015 04:34:00
MORT 02/02/2015 18:04:00
MORT 02/02/2015 18:59:00
JOHA 02/02/2015 03:35:00
LITMAR 02/02/2015 03:46:00
RODB 02/02/2015 10:15:00
IE: File 2
Driver ID Vehicle# Login Date/Time Logout Date/Time
MORT 040 02/02/2015 05:20:09 02/02/2015 15:11:25
JOHA 318 02/02/2015 01:35:00 02/02/2015 05:35:00
LITMAR 5129 02/02/2015 01:46:00 02/02/2015 05:46:00
RODB 5101 02/02/2015 01:15:00 02/02/2015 11:15:00
I have daily temperature data for over 50 stations ranging from 2005-2012. I am looking for a formula that will give me the station Name, date closest 12/31/2012, and the temperature value for that date.
I tried vlookup/match/INDEX lookup up or match station than I am unable to index date
My column A is DATE, column B has letters A-H which can repeat and varies with the dates, columns C and D have integers. I am looking for a formula to be in a cell in column E that can give me the value from either C/D if a cell value in column F (can be letters from A-H) matches with the letter in column B and the date is the MOST RECENT DATE. Thanks to everyone who can help me.
Hi,
can anybody help me solve following problem.
Table1
item no - start date - end date - value
A 01.01.16 21.02.16 10
A 22.02.16 31.12.16 20
B 01.01.16 31.12.16 30
Depending on a specific date within the range, the correct value shall be used in following formula:
item no date value
A 20.02.16 VLOOKUP(item no;Table1;4)- Result 10
B 25.02.16 VLOOKUP(item no;Table1;4)- Result 30
A 25.02.16 VLOOKUP(item no;Table1;4)- Result 20
Hi Oscar,
I have
name1 | division |...other columns...| start date of vacation | end date of vac (but start date and end date of vacation are in more than two columns (they are using more than one part of vacation ex: start | end, start|end...)
i need to extract name1, division, start date, end date for any given date
Thank You
counting people on vacation
Hi,
I have a set date that is a baseline. I can move it 60days either side as a maximum.
Qs. I have 300 rows of data each has its own populated baseline date (say row 1 is 30-6-2016 and row 300 is 25-9-2023) but want to align dates by moving some forward and some back for efficiency.
Do I have to first individually make a start date and end date range on each row from the baseline date or is there a formula that can say -60 days (30-6-2016) +60 days
Hi Oscar,
Could you help me solve this problem. I have sets of data both our date based. i.e, what I have below.
4.1.1990 33234 2.1.1990 2345
5.1.1990 33245 4.1.1990 2356
6.1.1990 33265 5.1.1990 2354
7.1.1990 33678 6.1.1990 2367
7.1.1990 2314
What I am trying to do is match the data from the second set of data to the first set of data so that the date lines up. The extra days in the second data will be disregarded as i don't need them.
I have tried many different types of formulas but till not can't find a solution.Doing this manually takes way to long as some of my data spans 25 years.
If you can help I would be very thankful :-)
Hi Merlin
I would separate the contents of one cell into multiple columns, on both data sets. Text-to-columns allows you to do that.
You find Text-to-columns on tab "Data", on the ribbon.
Use INDEX + MATCH to line the dates up.
Formula in cell C1:
=INDEX(Sheet2!A$1:A$5, MATCH(Sheet1!$A1, Sheet2!$A$1:$A$5, 0))
Copy cell C1 (not formula) and paste it to remaining cells in column C and D.
I have a date range A1(12/06/2016) to B1(20/10/2016) and date of joining(DOJ) in C1(15/07/2000). In D1 I want to return 20 otherwise "" if only the DOJ(D1) 15th July matches the date range regardless of the year.
Note: The date format is in (dd/mm/yyy).
I have searched the web but yet to find an answer of this kind. An answer in IF formula(excel) will be great since I don't understand SQL.
Thanks in advance.
John Sanil
John Sanl
This formula works for me:
Thanks Oscar,
Your formula gave me a good relief which I had been searching the web for a long time.
But Sorry, I think, I misinterpreted the way I put in the query.
D1 works perfectly till 31/12/2016 but has it crosses over to the next year say 01/03/2017 (dd-mm-yyy) the answer shows blank even though date of joining ie. 15th July is within date range 12/06/2016 to 01/03/2017. Is there a possible solution for this?
Thanks in advance for the answer
John
John Sanl
I understand, this formula seems to do the work:
=IF(YEAR(A1)=YEAR(B1), IF((DATE(1900,MONTH(A1), DAY(A1))<=DATE(1900,MONTH(C1), DAY(C1)))*(DATE(1900,MONTH(B1), DAY(B1))>=DATE(1900, MONTH(C1),DAY(C1))),20,""), IF((DATE(1900,MONTH(A1), DAY(A1))>DATE(1900, MONTH(C1),DAY(C1)))*(DATE(1900, MONTH(B1), DAY(B1))
Please help
=IFERROR(INDEX(B$4:B$16;SMALL(IF(($B$24>=$I$4:$I$16)*($B$24=$N$4:$N$16)*($B$24<=$P$4:$P$16);MATCH(ROW($B$4:$B$16);ROW($B$4:$B$16)));ROW(A1)));"")
how can I combine these two ranges into one (actualy i have 4 - I and J, N and P,....)? It needs to work like an OR function
Thank You
=IFERROR(INDEX(B$4:B$16;SMALL(IF(($B$24>=$I$4:$I$16)*($B$24=$N$4:$N$16)*($B$24<=$P$4:$P$16);MATCH(ROW($B$4:$B$16);ROW($B$4:$B$16)));ROW(A1)));"")
getting a value error using formula below:
=INDEX($B$2:$B$77,MIN(IF((DATEVALUE(RIGHT(C2:C77,LEN(C2:C77)-FIND("/",C2:C77)))>=C80)*(DATEVALUE(LEFT(C2:C77,FIND("/",C2:C77)-1))<=C80),MATCH(ROW($C$2:$C$77),ROW($C$2:$C$77)),"")))
tony,
Use "Evaluate Formula" on tab "Formulas" on the ribbon.
That will tell you where the error is.
Great Thanks to you as the solutions provided are terribly simple after days of searching for something similar!
Hi, thanks for the formula. In the case of "Match a date when a date range is entered in two cells"
What is the value if no range is matched? How can the formula return a default value in case it doesn't match any range?
Thanks in advance!
Ray
How to retrieve information from another sheet?
Match name by latest 4 max dates.
Sheet 1 has information
ie ; Column A Name & appears 20 times
; Column B Dates & same date appears 100 times
; Column C Distance
; Last Column is Z
Sheet 3 formula match name with latest 4 dates & across columns to last column Z
; Column A6 references Name ; Formula Column N6 Max Date ; M6
; Formula Column N7 2nd max Date ; O7
; Formula Column N8 3rd max Date ; O8
; Formula Column N9 4th max Date ; O9
Formula eg; =VLOOKUP(A6,'QLD RESULTS'!A:A,1,FALSE)
=VLOOKUP(A6,'QLD RESULTS'!A:B,2,FALSE)
=VLOOKUP(A6,'QLD RESULTS'!A:C,3,FALSE)
Regards
Tony
=INDEX($B$3:$B$6, SMALL(IF(($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6), MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6))), ROW(A1)))
What is the meaning of ROW(A1)?
Can you also give step by step break down of this formula? I am having hard time understanding this.
Hi Oscar,
Need your help with figuring out a formula.
I have a sheet 1 with 4 columns and following values:
Date start time End Time Analyst
1/5/2021 1:30 PM 4:00 PM Analyst 1
1/5/2021 4:00 PM 6:30 PM Analyst 2
1/6/2021 6:30 PM 9:00 PM Analyst 3
Sheet 2 contains just 1 column:
DateandTime
1/6/21 7:53 PM
1/7/21 7:03 PM
1/5/21 2:51 PM
I want to find to populate the name of the Analyst in sheet 2 if Dateandtime value matches any of the values in Sheet1. For instance, 1/6/21 7:53 PM this value should give me Analyst3 and 1/5/21 2:51 PM should give me Analyst1.
Please help to find out the formula.
Thanks,
Jasmeet
Hi Oscar
Would you please help with a formula.
I have a historical price table for say item ‘A’ where prices have increased at different times over the last 10 years.
Let’s say Column A shows the date of each price change and Column B the corresponding new price.
How would I write a formula to tell me what the price would have been if I enter a specific date in a cell?
Thanks
Sam
First of all I'd like to thank you for giving me exactly what I needed.
The only problem I'm facing is, when formula can't find a matching date, it shows #NUM! Error.
Here's the code (I removed the end date because I didn't need it)
{=INDEX($B$3:$B$10,SMALL(IF(($C$12=$C$3:$C$10),MATCH(ROW($B$3:$B$10),ROW($B$3:$B$10))),ROW(A1)))}Also here's the screenshot from your Workbook
https://postimg.cc/r0nHcF9M
I tried
{=IF(INDEX($B$3:$B$10,SMALL(IF(($C$12=$C$3:$C$10),MATCH(ROW($B$3:$B$10),ROW($B$3:$B$10))),ROW(A1)))="0","",INDEX($B$3:$B$10,SMALL(IF(($C$12=$C$3:$C$10),MATCH(ROW($B$3:$B$10),ROW($B$3:$B$10))),ROW(A1))))}But no luck. I want to show empty cell if formula returns no results.
Hi Oscar,
Need your help with figuring out a formula.
I have a sheet 1 with 4 columns and following values:
Suppler Name Item Date WEF Rate
Chandu Vegetables Bhindi 01-Jan-23 13.00
Chandu Vegetables Bhindi 10-Jan-23 19.00
Hayat Supplier Bhindi 02-Jan-23 12.00
Hayat Supplier Bhindi 06-Jan-23 14.00
Hayat Supplier Bhindi 08-Jan-23 18.00
Kishore Vegetable Bhindi 08-Jan-23 17.00
Kishore Vegetable Tomato 08-Jan-23 9.3
Shakti Tomato Tomato 01-Jan-23 9.35
Shakti Tomato Tomato 05-Jan-23 9.40
Shakti Tomato Tomato 10-Jan-23 9.50
Super Vegetables Tomato 01-Jan-23 9.00
Super Vegetables Tomato 05-Jan-23 9.25
Super Vegetables Tomato 08-Jan-23 8.00
Super Vegetables Tomato 10-Jan-23 9.65
Vikrant Fruits Bhindi 10-Jan-23 18.50
Sheet 2 Need Rate for following
Suppler Name Item Date WEF Rate?
Super Vegetables Tomato 02-Jan-23
Hayat Supplier Bhindi 04-Jan-23
Chandu Vegetables Bhindi 05-Jan-23
Hayat Supplier Bhindi 07-Jan-23
Kishore Vegetable Tomato 08-Jan-23
Kishore Vegetable Tomato 09-Jan-23
Kishore Vegetable Bhindi 09-Jan-23
Chandu Vegetables Bhindi 10-Jan-23
Vikrant Fruits Bhindi 10-Jan-23
Shakti Tomato Tomato 10-Jan-23
Chandu Vegetables Bhindi 11-Jan-23
Vikrant Fruits Bhindi 11-Jan-23
I want to find to populate the Rate in sheet 2 if Supplier Name, Item Name matches while Date matches or it should take Previous rate .
Please help to find out the formula.
Thanks,
Sohel Ahmed
Sohel Ahmed,
Here is a formula I think you are looking for:
As long as your dates are sorted in ascending order and into groups based on "Supplier Name" and "Item" the above formula will work.
Hi there,
Need a hand with a similar date formula
I have one sheet with 2 columns with the following;
FY & Date Range
2021-2022 01/04/2021 - 31/05/2022
2022-2023 01/04/2022 - 31/05/2023
2023-2023 01/04/2023 - 31/05/2024
I have a date range which I would need the FY.
For example;
I have a date range 05/05/2022-25/12/2022 (this would be FY 2022-2023) is there a formula for this?
Also, if the date range goes over 2 FY what would be the best formula?
for example;
25/02/2023 - 19/06/2023 (FY are 2022-2023 & 2023-2024)
Thanks
Qas,
I created helper columns to keep the formulas as small as possible:
Qas.xlsx