So I have a pretty long column with name dates and hours, like this:
14/01/2017 03:30
(They are DD/MM/YYYY HH:MM)
I need to get the month number and show it as text, like January, February, and so on. If I format the destination cell as 'MMM' all the column cells show January even if the given month is 2, 3, or let's say 12. I also tried the formula =TEXT(L2,"mmmm") (since the given info come from column F.
Any ideas?
sounds like your date and time are stored as text and not in excels serial date format. You can test this by changing the format of the cell to general and seeing if anything happens if nothing changes then its text. another way would be to use the formula =istext(A1) where A1 is a cell with a date in it.
Now ASSUMING you do have dates in text format, I would suggest converting them to excels date serial. There are numerous ways of doing this. I will show you one way.
This will strip each component of the text out and then recombine it. We will assume your date in is A1
Strip out the year
=MID(A1,7,4)
Strip out the month
=MID(A1,4,2)
Strip out the day
=LEFT(A1,2)
Now to recombine it:
=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))
That will give you the date in an integer format that excel can then turn around and display as needed.
In order not to lose the time component you should strip that part off as well and combine it with the date. Remember the date is an integer, and the time is everything after the decimal. Think of time as a fraction of a day.
To get time out use:
=RIGHT(A1,5)
and to convert it to excel time:
=TIMEVALUE(RIGHT(A1,5))
and to get the two together, simply add:
=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2)+TIMEVALUE(RIGHT(A1,5))
The key part to this whole thing is you will need to format that cell to display what you want. you can either choose a preformatted style from the drop down menu, or you can apply custom formatting. Setting the custom format to MMMM should display just the full name of the month.
IF you actually need the text of the month and not just the format, then you could use the formula from your question:
=TEXT(<insert appropriate formula from above>,"mmmm")
