Forum Discussion
Shorter way of extracting and matching date
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! 😉
You can also use TEXT([@[StartDate]];"jjjj\_mm")
The \ tells Excel to treat the _ after it as a literal character.
HansVogelaar Even better! This had a strange but positive side effect: I had to note the month as "mm" instead of "mmm" as explained above. which saves another character on the 19 characters saved additionally. That's 117 characters per cell less in total. On over 50,000 lines and dozens of columns, that makes a pretty huge difference 🙂