Need Formula to Convert Date/Timestamp to Month-Year

[kcclancy]

Remedy system exports dates in Date and Time format (8/1/2009 12:19:59 PM) I want to convert this into a Month-Year format for a pivot table. I tried reformatting the column to Mon-YY and then Copy > Paste Special > Values. But it doesn't work. I can't seem to get rid of the date/time. I even tried =LEFT to try to strip the time out. But then it just gives me Excel numeric code.

Anyone have any ideas for a formula that will turn "8/1/2009 12:19:59 PM" into Aug-09?

 

[barry houdini]

Try

=TEXT(A1,"mmm-yy")

 

[Domski]

Hi,

Have you tried grouping the date/time values by month and year in the pivot?

Dom

 

[c_m_s_jr]

Welcome to the board.

A few options:

1) =DATE(YEAR(A1),MONTH(A1),1) will set all dates to the 1st
2) You could use the group by funciton of the pivot table, then you don't have to add a columne for month year
3) =TEXT(A1,"mm-yy")

 

[kcclancy]

It worked. Thanks Barry!!!

 

[Domski]

There's nothing wrong with using the formula Barry suggested but the grouping method would save you bothering having to add one at all.

Dom