Get Month Name from single number

[jazminecat]

Hi all - I have an excel spreadsheet that has the month as a single number on one worksheet, and I'd like to show the name of that month on another worksheet.

This worksheet is automatically populated with a bunch of other stuff using spreadsheet server, and I don't have the option of changing the way the month is displayed on the first sheet. The spreadsheet server software requires a single number in that first field.

So, this first field is G4 on my first worksheet, and it's a text field. I can't change the format of it either, or spreadsheet server won't work correctly. So, let's say we're doing month end reports for october. G4 will then be 10.

I need another worksheet to look at G4, and return "October"

Can it be done?

thanks in advance!

 

[AnotherHiggins]

=DATE(2006,Sheet1!A1,1)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[jazminecat]

that returns 38687, if sheet1a1 = 10.

I need it to return "October"

 

[TonyJollans]

[blue][tt] =TEXT(DATE(,Sheet1!G4,1),"MMMM")[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[AnotherHiggins]

Or just select the column, go to Format > Cells > Number > Custom and type in MMM (or MMMM).

That keeps the cells formatted as dates, not text.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[TonyJollans]

That will always give January, John, because it takes the numbers (1 - 12) as date serials - representing early January 1900.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[AnotherHiggins]

Try it, Tony. It works for me!

Put in =DATE(,Sheet1!A1,1)
(I didn't realize you could just leave the year blank - that's handy)

Format the cell as MMM. If Sheet1!A1 contains '2 (text two), then the formula returns Feb.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[GlennUK]

John, I think Tony means "format the column" won't work on the original data.

Tony, I think John means use the inner part of your formula and format that column.

Personally, I'd tell the user to use proper dates.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[TonyJollans]

A-ha! I see what you mean now, John. Thanks Glenn.

I don't think its a user entering this - the month number comes from some other software.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[AnotherHiggins]

Thanks for being an interpreter, Glenn.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Hi,

[tt]
=text(DATE(year(today()),Sheet1!A1,1),"mmmm")
[/tt]


Skip,

_[red][/red]

 

[GlennUK]

My pleasure John and Tony :-D

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[jazminecat]

Skip, that's working! Thank you all for your help! Tony, you're right, the number comes from other software. We use a product that pulls data from the AS400 into an excel spreadsheet, and another product to populate that spreadsheet with specific data for each department, and it also emails it automatically to each department. So yeah, I can't change how it's formatted. But this

=text(DATE(year(today()),Sheet1!A1,1),"mmmm")


from Skip seems to be doing the trick. Thank you again!

 

[TonyJollans]

.. which is what I posted in the first place (except I left the year to default to zero as it is academic what year you use)

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[AnotherHiggins]

... which is what I posted in the first place (except I formatted the cell instead of using TEXT)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[TonyJollans]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association