Excel Display certain dates


Joined
Feb 23, 2018
Messages
52
Reaction score
14
I have a range(row) of dates for a whole year with a letter under each date. The letters go from A - P

On another sheet I want to show only the dates that only have the letter H under them. I want these dates to appear next to each other.

H can appear under any date.
 
Last edited:

Becky

Webmistress
Administrator
Joined
Mar 25, 2003
Messages
7,356
Reaction score
1,481
I think this could be done using TEXTJOIN, as follows;

=TEXTJOIN(",",TRUE,IF(A2:D2="H",TEXT(A1:D1,"dd-mm-yy"),""))

where A2:D2 is the row with the letters, and A1:D1 is the row with the dates. This is an array formula, so you need to press Ctrl + Shift + Enter after editing it. The TEXT part of the formula ensures the data is returned in the form of a date rather than serial number - you can change the dd-mm-yy to whatever you like (just make sure it has the quotes around it).

Hope this helps! :)
 
Joined
Feb 23, 2018
Messages
52
Reaction score
14
OK. Never come across TEXTJOIN before.

I have put it in the spreadsheet with the required ranges and it returns #NAME?

The version of EXCEL 2016 I'm using doesn't recognise TEXTJOIN :-(

Also Id need the dates to appear in separate cells......
 
Last edited:

Becky

Webmistress
Administrator
Joined
Mar 25, 2003
Messages
7,356
Reaction score
1,481
Ah, what a shame!

I'm just about to head out so don't have time to look at this right now, but I imagine you could do something with the LOOKUP function or maybe INDEX/MATCH (HLOOKUP won't work because that requires the data you're looking up to be on the first row of the range). I'll have a think and try and get back to you when I can!
 
Joined
Feb 23, 2018
Messages
52
Reaction score
14
Ah, what a shame!

I'm just about to head out so don't have time to look at this right now, but I imagine you could do something with the LOOKUP function or maybe INDEX/MATCH (HLOOKUP won't work because that requires the data you're looking up to be on the first row of the range). I'll have a think and try and get back to you when I can!

OK No problem.

Thank you!
 
Joined
Feb 23, 2018
Messages
52
Reaction score
14
I have got this to work in a fashion using
{=(IF(L48:Q48="h",L47:Q47,""))} as an array. L48-Q48 contain the letters some of which are H and L47-Q47 contain dates.

It leaves blanks where there is no H. I want to show only the dates is consecutive cells.

I have also tried INDEX MATCH
="Shift = "&INDEX($L$44:$Q$45,MATCH("Shift",$K$45:$Q$45,0),1)& ", Date: " & TEXT(INDEX($L$44:$Q$45,MATCH("Shift",$K$45:$Q$45,0),2),"d/mm/yy")

But this is reliant on me incrementing the number shown in bold to move on to the next cell.
Also it shows the H as a number and puts that and the date in the same cell.

Again any advice would be useful and genuinely appreciated!!
 
Joined
Feb 23, 2018
Messages
52
Reaction score
14
If you are struggling I have no hope!! :)

I will have a look and let you know. On the face of it.....it looks possible!

Thank you
 
Joined
Feb 23, 2018
Messages
52
Reaction score
14
Hi Becky

OK I have had a go with that formula. By manipulating the data I have I can get the dates with H under them only into a column format which is fine.

So a big THNAK YOU for your help with that.

Now all I have to do is assign the values and manipulating that my other posts have given me to make a Holiday Sheet work completely automatically from a rota. Simples!
 

Becky

Webmistress
Administrator
Joined
Mar 25, 2003
Messages
7,356
Reaction score
1,481
Glad to hear it worked! :thumb:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top