What denotes a month in cell format?

[dindigul]

I have a list, 3,4,6,7,8,10,12. I want to apply a cell format which will
result in months, that is, March, April, June, July, August, October,
December. What should be specified? Thanks

 

[Peo Sjoblom]

Not possible using formats, you would need a formula like

=INDEX({"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"},MATCH(A1,{1;2;3;4;5;6;7;8;9;10;11;12},0))


but it is not possible to format a cell with 10 to return October

 

[Ragdyer]

Just another way:

=CHOOSE(A1,"January","February","March","April","May","June","July","August","September","October","November","December")

 

[Peo Sjoblom]

Shorter but alas volatile <bg>

 

[Ragdyer]

I don't believe it is Peo!

Doesn't generate a <Save> request when opening and closing.

 

[Gord Dibben]

This is the latest list.......pre 2007..........of Volatile Functions.

AREAS()
OFFSET()
CELL()
INDIRECT()
NOW()
TODAY()
RAND()


Gord

 

[Peo Sjoblom]

You are correct, wonder where I got that from?

Peo

 

[Ragdyer]

In XL2k Gord, Areas() is *off* the list ... AND ... INFO() is included.

This is what I have ... don't remember from where/who it came from:

OLD NEW
1 Areas
2 Index
3 Offset Offset
4 Cell Cell
5 Indirect Indirect
6 Rows
7 Columns
8 Now Now
9 Today Today
10 Rand Rand
11 Info

Would be nice if someone would/could verify this list!

 

[Ragdyer]

Since you're just a young kid, you can't use that "old age" excuse that Gord
and I use so often.<bg>

 

[Roger Govier]

Hi Rick

Your new list is applicable to XL2002, 2003 and 2007

 

[Ragdyer]

Since I'm on an XL2k machine right now Roger, we can add that version to the
list also.
Thanks!

 

[Rick Rothstein \(MVP - VB\)]

Just another way:

=CHOOSE(A1,"January","February","March","April","May","June","July","August","September","October","November","December")

Would this shorter formula be acceptable?

=TEXT(DATE(1900,A1,1),"mmmm")

Rick

 

[RagDyeR]

That's neat!

I like that.<g>

Can also be:

=TEXT(DATE(,A1,1),"mmmm")
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Just another way:

=CHOOSE(A1,"January","February","March","April","May","June","July","August","September","October","November","December")

Would this shorter formula be acceptable?

=TEXT(DATE(1900,A1,1),"mmmm")

Rick

 

[Rick Rothstein \(MVP - VB\)]

Can also be:

=TEXT(DATE(,A1,1),"mmmm")

I did not realize that the Year argument for the Date function was
optional... the help file doesn't seem to indicate that, but your formula
without it works fine. As it turns out, the day is optional too, so the
above could be written like this also...

=TEXT(DATE(,A1+1,),"mmmm")

Not much gain (actually, it costs another keystroke), but I thought it
interesting.

Rick

 

[Peo Sjoblom]

You might want to hard code 1904 as (or any year later than 1903) year
since neither formula will work if the 1904 date system is checked under
tools>calculations (or if the computer is a Mac)


=TEXT(DATE(1904,A1,1),"mmmm")


should work for all options

 

[Peo Sjoblom]

That should have been

Tools>options>calculation

 

[Rick Rothstein \(MVP - VB\)]

You might want to hard code 1904 as (or any year later than 1903) year

since neither formula will work if the 1904 date system is checked under
tools>calculations (or if the computer is a Mac)

=TEXT(DATE(1904,A1,1),"mmmm")

should work for all options

I don't use that date system and would have never thought about it in a
million years. Thanks for making note of it. Funny, originally, I was going
to use 2000 as it doesn't matter (except for the date system setting that
you noted), but thought the year for day would "look" better.

I am not all that familiar with the problems that can arise between these
two date systems, but I just did a quick experiment and it looks like years
outside of the 1900-1904 span (for example, 1899) work fine. In particular,
this worked under both date systems...

=TEXT(DATE(100,A1+1,),"mmmm")

Rick

 

[Rick Rothstein \(MVP - VB\)]

You might want to hard code 1904 as (or any year later than 1903) year

I don't use that date system and would have never thought about it in a
million years. Thanks for making note of it. Funny, originally, I was
going to use 2000 as it doesn't matter (except for the date system setting
that you noted), but thought the year for day would "look" better.

..... but thought the year for day ONE would "look" better...

Rick

 

[Peo Sjoblom]

I don't use that date system and would have never thought about it in a
million years. Thanks for making note of it. Funny, originally, I was
going to use 2000 as it doesn't matter (except for the date system setting
that you noted), but thought the year for day would "look" better.

I am not all that familiar with the problems that can arise between these
two date systems, but I just did a quick experiment and it looks like
years outside of the 1900-1904 span (for example, 1899) work fine. In
particular, this worked under both date systems...

=TEXT(DATE(100,A1+1,),"mmmm")

1899 does not exist in Excel, Excel dates starts with January 0, 1900,

DATE(100,A1+1,)

will return year 2000

YEAR(1899)

will return 1905

Another thing to think about when it comes to dates is that quite a lot of
date formulas dealing with first/last day of current/previous/next month
will return NUM errors if one uses Lotus transition formulas under
tools>options>transition

example


=DATE(YEAR(TODAY()),MONTH(TODAY())+1,)

will return the last day of the current month but if you do
tools>options>transition and select

transition formula evaluation it will return #NUM!

and believe me, there are a lot of workbooks out there that were once
created in Lotus 123



I once spent a whole day auditing a timesheet I made because it was
originally created in Lotus 123

so whenever I see the num error I always check under transition first

 

[Rick Rothstein \(MVP - VB\)]

1899 does not exist in Excel, Excel dates starts with January 0, 1900,

DATE(100,A1+1,)

will return year 2000

YEAR(1899)

will return 1905

My compiled VB roots (well, VBA shares this too) led me to think the year
ranges there applied in the formula world too (look up DateSerial as an
example in the VBA help files and look at the description for the Year
argument; it says the earliest recognizable year is 100). Obviously, it
doesn't, but the fact that I was only looking at the month value, and
getting proper results, fooled me into thinking they were the same.

Okay, with the above noted, we can take advantage of this fact to shorten
the formula I originally posted even more (at the cost of decipherability,
of course<g>). Since year 4 becomes year 1904..

=TEXT(DATE(4,A1,1),"mmmm")

will work for both date systems.

Another thing to think about when it comes to dates is that quite a lot of
date formulas dealing with first/last day of current/previous/next month
will return NUM errors if one uses Lotus transition formulas under
tools>options>transition

and believe me, there are a lot of workbooks out there that were once
created in Lotus 123

I once spent a whole day auditing a timesheet I made because it was
originally created in Lotus 123

so whenever I see the num error I always check under transition first

A good procedure to keep in mind.


Rick