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

mrtbll

New Member
Joined
Aug 26, 2020
Messages
5
Office Version
  1. 365
Platform
  1. MacOS
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!
IMG_1746 2.JPG
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,515
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 26, 2020
Messages
5
Office Version
  1. 365
Platform
  1. MacOS
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

New Member
Joined
Aug 26, 2020
Messages
5
Office Version
  1. 365
Platform
  1. MacOS
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

Well-known Member
Joined
Dec 30, 2008
Messages
14,515
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Aug 26, 2020
Messages
5
Office Version
  1. 365
Platform
  1. MacOS
Thank you! You were very helpful, I finally understood it! :)
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,515
Office Version
  1. 365
Platform
  1. Windows
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 (y)
 

Forum statistics

Threads
1,147,743
Messages
5,742,943
Members
423,765
Latest member
PaulD1984

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top