Forum Discussion

Copper Contributor

Sep 11, 2020

Solved

Shorter way of extracting and matching date

Hi all! Let's say my current workbook is named CurrentWB and the headers I'm working in are formatted like so: X_2020_01 (X = variable prefix, 2020 = year, 01 = month) Part of my formula to matc...

Formulas and Functions

HansVogelaar
Sep 11, 2020
Timmmmaaahh

=IF(RIGHT(CurrentWB[#Headers];7)=TEXT([@[StartDate]];"yyyy_mm");...

MVP

Sep 11, 2020

=IF(RIGHT(CurrentWB[#Headers];7)=TEXT([@[StartDate]];"yyyy_mm");...

Timmmmaaahh
Copper Contributor
Sep 14, 2020
HansVogelaar Thank you, that looks really great. But for some reason it won't detect the year in my case.
The dates in StartDate are formatted as "15/01/2020 0:00:00" but even when I format it like "15/01/2020", the result remains the same.
=TEXT([@[StartDate]];"yyyy_mm")
returns:
yyyy 1
I tried "dd" and it correctly returns 15. I tried "yy", "YYYY" and "YY" but they all fail to return the year. Have I broken the document somehow?

Edit: I tried creating a new document after restarting Excel and the result is the same. Perhaps something in my regional settings?
- HansVogelaar
  MVP
  Sep 14, 2020
  Timmmmaaahh
  
  You'll have to replace yyyy with the letter used for year in your language.
  
  For example, in German or Dutch, it should be jjjj (for Jahr/jaar).
  
  And in French, Italian, Spanish and Portuguese it should be aaaa (for an/anno/año/ano)
  - Timmmmaaahh
    Copper Contributor
    Sep 14, 2020
    HansVogelaar Success! It just needed some adjustments.
    Despite my system's date formats being set to "yyyy", Excel seems to ignore this. "jjjj" did indeed do the trick, thanks!
    TEXT([@[StartDate]];"jjjj_mm") returned 2019 1, which doesn't match the needed 2019_01. I fixed this with the following adjustment:
    SUBSTITUTE(TEXT([@[StartDate]];"jjjj_mmm");" ";"_")
    
    Van harte bedankt! Thank you very much! 😉

Resources