Sep 11 2020 05:11 AM
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 match if both month and year are the same compared to the 'StartDate' column in the table I've created like so:
IF(AND((MONTH(DATEVALUE(RIGHT(CurrentWB[#Headers];2)&"/"&MID(CurrentWB[#Headers];3;4))))=(MONTH([@[StartDate]]));(NUMBERVALUE((MID(CurrentWB[#Headers];3;4))))=(YEAR([@[StartDate]])))
I'm getting the feeling there could be a shorter way of doing this check :face_with_rolling_eyes: Any ideas?
Cheers
Timmy
Sep 11 2020 05:48 AM
SolutionSep 14 2020 02:47 AM - edited Sep 14 2020 02:59 AM
@Hans Vogelaar 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?
Sep 14 2020 03:26 AM
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)
Sep 14 2020 04:36 AM
@Hans Vogelaar 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! ;)
Sep 14 2020 04:53 AM
You can also use TEXT([@[StartDate]];"jjjj\_mm")
The \ tells Excel to treat the _ after it as a literal character.
Sep 14 2020 05:50 AM
@Hans Vogelaar 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 :)
Sep 11 2020 05:48 AM
Solution
=IF(RIGHT(CurrentWB[#Headers];7)=TEXT([@[StartDate]];"yyyy_mm");...