What formula to use if I need to extract the name of a month from a tab?

[mrtbll]

I'm really new to Excel, but I'm currently trying to get the hang of it because I have a uni test in 4 days. The problem asks me to extract the name of the current month in French from a tab that reports the name of all the months in French obviously (I think I uploaded a photo of the tab). I can't understand what formula to use and I've been trying to resolve this problem for hours... I really need help, thank you!

 

[jasonb75]

How exactly is the problem phrased?

The way that you have phrased your question implies that you might be working with English language settings rather than French settings, compounding the difficulty of the problem.

My instinct would be to bypass the table and use the TEXT function with the current date and a bit of creative formatting, although I very much doubt that it would be accepted given that use of the table has been requested and specific methods are often expected.

Also, the formula depends on the formatting of the table, are the names entered as text or as dates formatted as mmmm? If you select one of the cells, and look at the formula bar, formatted dates will appear there as numeric e.g. 26/08/2020 where as text will appear the same as it does in the cell.

There is one thing that comes to mind to extract the date from the table, but it assumes a consistent layout with the months in the order shown. Also, it might not fall in line with what is expected. If I mention the functions INDEX, MONTH, TODAY, MOD and INT does that help in any way?

 

[mrtbll]

Thank you for your answer!
the problem is phrased like this: "Write down the formula that gives you the current month's name in French". I'm not working with French setting. Unfortunately I can't give you the formatting of the table because my professor wants us to work on this problems on paper (so we don't actually use Excel).
The only thing that comes to my mind is using the formula =IF for every French month but the entire formula would be too long (=IF(MONTH(TODAY())=1;B2 etc...)
I didn't think to add the solution, cause I can't understand it and it doesn't work on my Excel, but here it is: =INDIRECT(IF(MONTH(TODAY())<7; "B"; "C") & (MONTH(TODAY())+IF(MONTH(TODAY())<7;1;-5)))
hope that helps!

 

[mrtbll]

Forgot to mention that I do actually know the functions MONTH, TODAY and INT, but I can’t use INDEC and MOD as I’ve never heard of them.

 

[jasonb75]

If you're using English excel then you would need to change the commas in the formula to semicolons.

To help you understand it, MONTH(TODAY()) returns the current month in numeric form, Jan =1, Feb =2, etc.
The logical test, <7 checks to see if the date is in the first 6 months of the year. If it is, the formula returns "B" if not it returns "C".

The next part is a little more complex, again it takes the current month number, but then it changes it. If the month is < 7 then it adds 1 (so Jan becomes 2, Feb 3, etc). If it is 7 or more then it subtracts 5 (Jul becomes 2, Aug 3, etc).

By joining the resulting number of the second part to the letter from the first part you get the location of the month in the table.
Using today (August 26th), the month is not < 7 so the result of the first part is "C" and the result of the second part is (8-5 = 3). Join them together and you have "C3" which INDIRECT converts to a valid cell location on the sheet and returns "aout" from C3.

 

[mrtbll]

Thank you! You were very helpful, I finally understood it!

 

[jasonb75]

You're welcome

If you're interested, here's the index formula as well, I'll let you have a look at it and see if you can figure it out.

=INDEX(B2:C7,MOD(MONTH(TODAY())-1,6)+1,INT((MONTH(TODAY())-1)/6)+1)

Remember you can access the built in help by pressing f1 in Excel, then searching for a function name to see some basic examples.

And the cheeky no table formula, =TEXT(TODAY(),"[$-fr-FR]mmmm")

Good luck with the test