SOLVED

Shorter way of extracting and matching date

%3CLINGO-SUB%20id%3D%22lingo-sub-1656944%22%20slang%3D%22en-US%22%3EShorter%20way%20of%20extracting%20and%20matching%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1656944%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet's%20say%20my%20current%20workbook%20is%20named%20CurrentWB%20and%20the%20headers%20I'm%20working%20in%20are%20formatted%20like%20so%3A%20%3CSTRONG%3EX_2020_01%3C%2FSTRONG%3E%20(X%20%3D%20variable%20prefix%2C%202020%20%3D%20year%2C%2001%20%3D%20month)%3CBR%20%2F%3E%3CU%3EPart%20of%3C%2FU%3E%20my%20formula%20to%20match%20if%20both%20month%20and%20year%20are%20the%20same%20compared%20to%20the%20'StartDate'%20column%20in%20the%20table%20I've%20created%20like%20so%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3EIF(AND((MONTH(DATEVALUE(RIGHT(CurrentWB%5B%23Headers%5D%3B2)%26amp%3B%22%2F%22%26amp%3BMID(CurrentWB%5B%23Headers%5D%3B3%3B4))))%3D(MONTH(%5B%40%5BStartDate%5D%5D))%3B(NUMBERVALUE((MID(CurrentWB%5B%23Headers%5D%3B3%3B4))))%3D(YEAR(%5B%40%5BStartDate%5D%5D)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CBR%20%2F%3EI'm%20getting%20the%20feeling%20there%20could%20be%20a%20shorter%20way%20of%20doing%20this%20check%20%3Aface_with_rolling_eyes%3A%20Any%20ideas%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3CBR%20%2F%3ETimmy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1656944%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1657106%22%20slang%3D%22en-US%22%3ERe%3A%20Shorter%20way%20of%20extracting%20and%20matching%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1657106%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F790241%22%20target%3D%22_blank%22%3E%40Timmmmaaahh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(RIGHT(CurrentWB%5B%23Headers%5D%3B7)%3DTEXT(%5B%40%5BStartDate%5D%5D%3B%22yyyy_mm%22)%3B...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1663556%22%20slang%3D%22en-US%22%3ERe%3A%20Shorter%20way%20of%20extracting%20and%20matching%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1663556%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BThank%20you%2C%20that%20looks%20really%20great.%20But%20for%20some%20reason%20it%20won't%20detect%20the%20year%20in%20my%20case.%3C%2FP%3E%3CP%3EThe%20dates%20in%20StartDate%20are%20formatted%20as%20%2215%2F01%2F2020%200%3A00%3A00%22%20but%20even%20when%20I%20format%20it%20like%20%2215%2F01%2F2020%22%2C%20the%20result%20remains%20the%20same.%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DTEXT(%5B%40%5BStartDate%5D%5D%3B%22yyyy_mm%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Ereturns%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eyyyy%201%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20tried%20%22dd%22%20and%20it%20correctly%20returns%2015.%20I%20tried%20%22yy%22%2C%20%22YYYY%22%20and%20%22YY%22%20but%20they%20all%20fail%20to%20return%20the%20year.%20Have%20I%20broken%20the%20document%20somehow%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEdit%3A%20I%20tried%20creating%20a%20new%20document%20after%20restarting%20Excel%20and%20the%20result%20is%20the%20same.%20Perhaps%20something%20in%20my%20regional%20settings%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1663647%22%20slang%3D%22en-US%22%3ERe%3A%20Shorter%20way%20of%20extracting%20and%20matching%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1663647%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F790241%22%20target%3D%22_blank%22%3E%40Timmmmaaahh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou'll%20have%20to%20replace%20yyyy%20with%20the%20letter%20used%20for%20year%20in%20your%20language.%3C%2FP%3E%0A%3CP%3EFor%20example%2C%20in%20German%20or%20Dutch%2C%20it%20should%20be%20jjjj%20(for%20Jahr%2Fjaar).%3C%2FP%3E%0A%3CP%3EAnd%20in%20French%2C%20Italian%2C%20Spanish%20and%20Portuguese%20it%20should%20be%20aaaa%20(for%20an%2Fanno%2Fa%C3%B1o%2Fano)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1663932%22%20slang%3D%22en-US%22%3ERe%3A%20Shorter%20way%20of%20extracting%20and%20matching%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1663932%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BSuccess!%20It%20just%20needed%20some%20adjustments.%3C%2FP%3E%3CP%3EDespite%20my%20system's%20date%20formats%20being%20set%20to%20%22yyyy%22%2C%20Excel%20seems%20to%20ignore%20this.%20%22jjjj%22%20did%20indeed%20do%20the%20trick%2C%20thanks!%3C%2FP%3E%3CP%3E%3CSTRONG%3ETEXT(%5B%40%5BStartDate%5D%5D%3B%22jjjj_mm%22)%3C%2FSTRONG%3E%20returned%20%3CSTRONG%3E2019%201%3C%2FSTRONG%3E%2C%20which%20doesn't%20match%20the%20needed%20%3CSTRONG%3E2019_01%3C%2FSTRONG%3E.%20I%20fixed%20this%20with%20the%20following%20adjustment%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3ESUBSTITUTE(TEXT(%5B%40%5BStartDate%5D%5D%3B%22jjjj_mmm%22)%3B%22%20%22%3B%22_%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVan%20harte%20bedankt!%20Thank%20you%20very%20much!%20%3CLI-EMOJI%20id%3D%22lia_winking-face%22%20title%3D%22%3Awinking_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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
best response confirmed by Timmmmaaahh (New Contributor)
Solution

@Timmmmaaahh 

 

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

@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?

@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)

@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!

@Timmmmaaahh 

 

You can also use TEXT([@[StartDate]];"jjjj\_mm")

The \ tells Excel to treat the _ after it as a literal character.

@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