Forum Discussion
Using Text function to obtain "Months"
Nice approach. Using the same idea:
=INDEX(TEXT(DATE(2022,SEQUENCE(12),1),"mmmm"),LEFT(A1,FIND("/",A1)-1))
Alternatively, with your improper date in A1 (assuming Excel 2021 or 365):
=LOOKUP(--LEFT(A1, SEARCH("/",A1)-1), SEQUENCE(12), TEXT(DATE(2022,SEQUENCE(12),1),"mmmm"))Nice approach. Using the same idea:
=INDEX(TEXT(DATE(2022,SEQUENCE(12),1),"mmmm"),LEFT(A1,FIND("/",A1)-1))- Even better HansVogelaar
- Perfect Guys, this is much easy approach. Thanks for your support HansVogelaar , Lorenzo
In response to your PM asking for explaination. As always, best way to understand is to decompose the formula
The logic is the following: you only want to get the MonthName from your improper EN Dates where the month number (1,2...,12) is before the 1st /
#1 A Year consists of 12 months, hence SEQUENCE(12) that returns an array of numbers from 1 through 12. When we nest this SEQ. in the DATE function we get an array of 12 dates (Jan 1st 2022, Feb 1st 2022.... Dec 1st 2022). That new array nested in the TEXT function returns the Month Names only (nb. I added [$-en-GB] in front of mmmm because my Regional Settings aren't EN)
#2 in G2, =LEFT(A2,FIND("/",A2)-1) simply returns what's before the 1st / in A2
#3 We have an array of Month Names in E2:E13. This is a https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531 so we reference it as E2#. So in I2: INDEX(E2#, G2)
Hope this helps
