Formula Date Question

[rjacmuto32]

I have 2 cells. A1 contains a Category (Facilities), and A2 contains the date (September 14, 2012)


when a I a formula to combine them together with some text, the date comes out as 41666 instead of Septeber 30, 2012 in the text.


=A1&"as of"&A2 gives me Facilities as of 41166. How can it be edited to give me Facilities as of September 14, 2012?

 

[Debraj]

Hi rjacmuto32!


Use

=A1 & "as of" & TEXT(A2,"MMMM DD, YYYY")

Regards,

Deb

 

[sdsurzh]

Hi,


Please try the below formula.


=A1&" as of "&TEXT(A2,"mmmm dd yyyy")


Thanks,

Suresh Kumar S

 

[rjacmuto32]

Perfect thanks. The 2nd one with the 4 MMMM worked.

 

[Debraj]

Hi rjacmuto32,


Happy to help..


PS: I guess.. you are talking about Capital letter "MMMMM".. not the small letter "mmmm"..

PPS: Is there any difference in output..

PPS2: of the "," is the difference.. lol...


Regards,

Deb

 

[sdsurzh]

Thanks Debraj


Both the formula will give the same output


Thanks,

Suresh Kumar S

 

[NARAYANK991]

Hi Deb ,


This is just BTW ; I know it's a typo in your earlier post , but =TEXT(Any date,"MMMM") and =TEXT(Any date,"mmmm") are the same ; both are different from =TEXT(Any date,"MMMMM") !


Courtesy Daily Dose of Excel ( www.dailydoseofexcel.com )


Narayan

 

[Debraj]

Hi Narayan..


Thanks for the catch..

I must admit.. your EYES are much sharper than your HEAD in avatar..


This is also just BTW, that I have learn a New thing, how to print the 1st character of a Month.. LOL..


Regards,

Deb