Excel Display certain dates

S

Simont485

Joined
Feb 23, 2018
Messages
67
Reaction score
24
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:
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! :)
 
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:
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!
 
Becky said:
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!
Click to expand...


OK No problem.

Thank you!
 
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!!
 
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
 
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!
 
Back
Top