Hi All

I am trying to get just the year and month of a date to display and I am using

=year(A1)
=month(A1)

the year is fine but the month appears as a number 1-12. I have tried
changing the field to custom month but this just give the wrong answer. I can
use a v-lookup to convert which is no problem but a bit messy (I remember in
2003 it worked fine)

Any suggestions greatly appreciated

cheers Rexmann

Try this for month:

=TEXT(A1,"mmm")

if you just want the first 3 letters of the month, or:

=TEXT(A1,"mmmm")

if you want the full month name.

You can combine the month and year, like so:

=TEXT(A1,"mmm-yyyy")

to give you SEP-2009, for example.

Hope this helps.

Pete

On Sep 8, 12:54*pm, rexmann wrote:
Hi All

I am trying to get just the year and month of a date to display and I am using

=year(A1)
=month(A1)

the year is fine but the month appears as a number 1-12. I have tried
changing the field to custom month but this just give the wrong answer. I can
use a v-lookup to convert which is no problem but a bit messy (I remember in
2003 it worked fine)

Any suggestions greatly appreciated

cheers Rexmann

Custom format (mmm) will work only if the cell contains actual date.

Try the below with date in cell A1 which returns a text value
=TEXT(A1,"mmm")

If this post helps click Yes
---------------
Jacob Skaria


"rexmann" wrote:

Hi All

I am trying to get just the year and month of a date to display and I am using

=year(A1)
=month(A1)

the year is fine but the month appears as a number 1-12. I have tried
changing the field to custom month but this just give the wrong answer. I can
use a v-lookup to convert which is no problem but a bit messy (I remember in
2003 it worked fine)

Any suggestions greatly appreciated

cheers Rexmann

Use text function

For abbreviate months
=Text(month(A1),"mmm")
or
For non-abbreviated months
=Text(month(A1),"mmmm")


"rexmann" wrote:

Hi All

I am trying to get just the year and month of a date to display and I am using

=year(A1)
=month(A1)

the year is fine but the month appears as a number 1-12. I have tried
changing the field to custom month but this just give the wrong answer. I can
use a v-lookup to convert which is no problem but a bit messy (I remember in
2003 it worked fine)

Any suggestions greatly appreciated

cheers Rexmann

=month() returns the number of the month.

If you want text, you could use:
=text(a1,"mmm")
or
=text(a1,"mmmm")

Or you could combine both the year and month and use something like:
=text(a1,"yyyy-mmm")

rexmann wrote:

Hi All

I am trying to get just the year and month of a date to display and I am using

=year(A1)
=month(A1)

the year is fine but the month appears as a number 1-12. I have tried
changing the field to custom month but this just give the wrong answer. I can
use a v-lookup to convert which is no problem but a bit messy (I remember in
2003 it worked fine)

Any suggestions greatly appreciated

cheers Rexmann

--

Dave Peterson

Thank you all, works perfect

Cheers Rexmann

"Dave Peterson" wrote:

=month() returns the number of the month.

If you want text, you could use:
=text(a1,"mmm")
or
=text(a1,"mmmm")

Or you could combine both the year and month and use something like:
=text(a1,"yyyy-mmm")

rexmann wrote:

Hi All

I am trying to get just the year and month of a date to display and I am using

=year(A1)
=month(A1)

the year is fine but the month appears as a number 1-12. I have tried
changing the field to custom month but this just give the wrong answer. I can
use a v-lookup to convert which is no problem but a bit messy (I remember in
2003 it worked fine)

Any suggestions greatly appreciated

cheers Rexmann

--

Dave Peterson