Using the MONTH, DAY, YEAR Function

  • Thread starter Thread starter Brent
  • Start date Start date
B

Brent

I've used the functions to return the value of the month,
day & year portions of a serial date value that is
entered in other cells of my worksheet. Now, I want to
display the MONTH value in cell B2 (for example) as text,
instead of its numeric value. When I format cell B2 to
display the month value of 4 (for example) as "Mar"
(mmm), instead it changes the value to 1 or jan (the
current month?)

Why is this? How can I achieve my desired result?

Brent
 
Brent,

The Month function returs an integer between 1 & 12.
When you format that cell as "mmm", it sees it as a serial
date of 1 thru 12 (which will always be January)

One way around this (there are other ways):

=CHOOSE(MONTH(B1),"Jan","Feb","Mar","etc")
(B1 being your date)

John
 
You can't use formatting if you have the month index like in 1 for January
and so on,
you need the full date otherwise you will get Jan for all months since it
sees it as days after
01/00/1900. So refer to the original date that you extracted the month
numbers from, or use

=INDEX({"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"D
ec"},A1)

where A1 holds the month numbers
 
Hi Brent,

Another way, assuming your month number in B1:

=DATE(2004,B1,1)
' Formatted as mmm

If you have the whole date somewhere (A1 for example):
=A1
'Formatted as mmm
Regards,

Daniel M.
 
Yet another:
=TEXT(DATE(1,B1,1),"mmmm")
with the month number in B1, or
=TEXT(B1,"mmmm")
with a complete date in B1. Both return strings, not dates.
 
Back
Top