Inserting IF formula in complex index/match formula to get results until certain date

SteveNL86

Board Regular
Joined
Nov 11, 2014
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
Dear reader,

I'm using this formula:
=IFERROR(INDEX(Rooster2021!$B$2:$B$5000;SMALL(IF(ISNUMBER(MATCH(Rooster2021!$N$2:$N$5000;Registratie!$B$3;0));MATCH(ROW(Rooster2021!$N$2:$N$5000);ROW(Rooster2021!$N$2:$N$5000));"");ROWS($A$4:$A4)));"")
And its working great, its giving me the matching results that I am after.

However, I'm keeping track of the last date the document was updated in cell B13. I'm trying to add an IF formula to show me matches that occur on or before the date specified in B13.

So if the dates in B2:B5000 in sheets 2021 are smaller or equal to the date in B13 it should give me the matching results, if the date is larger it should ignore the results.

Any help is much appreciated.
Kind regards.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,479
Office Version
  1. 365
Platform
  1. Windows
A quick test formula, what result do you get from this?
Excel Formula:
=COUNT(Rooster2020!$B$2:$B$5000)
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,479
Office Version
  1. 365
Platform
  1. Windows
That means that the dates in that sheet are in text format so the formulas can't recognise them properly. Any valid dates in the column would have been counted.

How is that data sourced? If it is imported from an external source then it might contain zero width control characters, most common from web pages but could be found elsewhere as well.
 

SteveNL86

Board Regular
Joined
Nov 11, 2014
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
Well thats the thing. I've got 6 different sheets with dates. And on my 'main' sheet the formula's are working to grab that dates from all 6 sheets apart from the 2021 sheet.

The data isn't from a webpage, its just manually entered data in excel. Ive actually selected column B and pasted the data as value.

When I'm using it like this:
VBA Code:
=IFERROR(INDEX(Rooster2021!$B$2:$B$5000;SMALL(IF(ISNUMBER(MATCH(Rooster2021!$N$2:$N$5000;Registratie!$B$3;0));MATCH(ROW(Rooster2021!$N$2:$N$5000);ROW(Rooster2021!$N$2:$N$5000));"");ROWS($A$4:$A4)));"")

It does give me the dates from Rooster2021.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,479
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It could be the way that the dates are entered.

If your regional settings are for dd/mm/yyyy format dates then a lot of dates entered in mm/dd/yyyy will not be valid. Looking right doesn't mean that they are.

If you enter dates in the wrong format using month names instead of numbers then none will be valid.

If the date in B13 is in an invalid format then all valid dates in the sheets will be < than that date. If B13 is valid but B2:B5000 are not then the reverse applies, which could explain why you are getting some results. Comparing 2 invalid dates will give some very unpredictable results
 

SteveNL86

Board Regular
Joined
Nov 11, 2014
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
You are absolutely right. The dates were originally entere as yyyy//mm/dd. So I changed the format to dd-mm-jjjj, then selected the entire column and pasted it as value thinking that would just overwrite it.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,479
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

So it is working now? 🤞:biggrin:
 

SteveNL86

Board Regular
Joined
Nov 11, 2014
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
Unfortunately no. I have figured out the issue is with that date in Rooster2021 column B.

When I use a If function its always saying the value in any B is either bigger or smaller then B13, regards of what cell in Column B. I haven't been able to figure out how to change the dates in Rooster2021 column B to get it working, but I'll keep trying some options.

Thanks for your help.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,479
Office Version
  1. 365
Platform
  1. Windows
how are the dates currently appearing in rooster 21 column B?

Text to columns might be able to fix it, depends how broken it is with anything you already tried.
 

SteveNL86

Board Regular
Joined
Nov 11, 2014
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
Datum
01-01-2021
01-01-2021
01-01-2021
01-01-2021
01-01-2021
01-01-2021
01-01-2021
01-01-2021
01-01-2021
01-01-2021
01-01-2021
01-01-2021
01-01-2021
01-01-2021
01-01-2021
01-01-2021
01-01-2021
02-01-2021
02-01-2021
02-01-2021

Ive tried using =TEXT(B2;"DD-MM-JJJJ") in another column and change B to that, but that doesn't seem to work.
Text to column also doesn't seem to work.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,778
Messages
5,626,827
Members
416,202
Latest member
donya ba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top