Forum Discussion
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 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 š Any ideas?
Cheers
Timmy
6 Replies
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?
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)
