Display certain dates

Excel Discussion in 'Microsoft Office' started by Simont485, Apr 12, 2018.

  1. Simont485

    Simont485

    Joined:
    Feb 23, 2018
    Likes Received:
    7
    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: Apr 12, 2018
    Simont485, Apr 12, 2018
    #1
    1. Advertisements

  2. Simont485

    Becky Webmistress Administrator

    Joined:
    Mar 25, 2003
    Likes Received:
    1,130
    Location:
    Manchester
    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! :)
     
    Becky, Apr 13, 2018
    #2
    1. Advertisements

  3. Simont485

    Simont485

    Joined:
    Feb 23, 2018
    Likes Received:
    7
    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: Apr 13, 2018
    Simont485, Apr 13, 2018
    #3
  4. Simont485

    Becky Webmistress Administrator

    Joined:
    Mar 25, 2003
    Likes Received:
    1,130
    Location:
    Manchester
    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, Apr 13, 2018
    #4
  5. Simont485

    Simont485

    Joined:
    Feb 23, 2018
    Likes Received:
    7

    OK No problem.

    Thank you!
     
    Simont485, Apr 13, 2018
    #5
  6. Simont485

    Simont485

    Joined:
    Feb 23, 2018
    Likes Received:
    7
    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!!
     
    Simont485, Apr 17, 2018
    #6
  7. Simont485

    Becky Webmistress Administrator

    Joined:
    Mar 25, 2003
    Likes Received:
    1,130
    Location:
    Manchester
    Becky, Apr 17, 2018
    #7
  8. Simont485

    Simont485

    Joined:
    Feb 23, 2018
    Likes Received:
    7
    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
     
    Simont485, Apr 17, 2018
    #8
  9. Simont485

    Simont485

    Joined:
    Feb 23, 2018
    Likes Received:
    7
    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!
     
    Simont485, Apr 17, 2018
    #9
    Becky likes this.
  10. Simont485

    Becky Webmistress Administrator

    Joined:
    Mar 25, 2003
    Likes Received:
    1,130
    Location:
    Manchester
    Glad to hear it worked! :thumb:
     
    Becky, Apr 17, 2018
    #10
    1. Advertisements

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 (here). After that, you can post your question and our members will help you out.