Results 1 to 6 of 6

Need Formula to Convert Date/Timestamp to Month-Year

This is a discussion on Need Formula to Convert Date/Timestamp to Month-Year within the Excel Questions forums, part of the Question Forums category; 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 ...

  1. #1
    New Member
    Join Date
    Oct 2009
    Posts
    2

    Question Need Formula to Convert Date/Timestamp to Month-Year

    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?

  2. #2
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,791

    Default Re: Need Formula to Convert Date/Timestamp to Month-Year

    Try

    =TEXT(A1,"mmm-yy")

  3. #3
    Board Regular Domski's Avatar
    Join Date
    Jan 2005
    Location
    Leeds, UK
    Posts
    7,178

    Default Re: Need Formula to Convert Date/Timestamp to Month-Year

    Hi,

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

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    - Guidelines For Posting

    - Mr Excel Articles and PodCasts

    - Display sheet using HTML Maker or Excel Jeanie

    - Something that makes me laugh

    - Please try to remember to use code tags when posting your VBA code: [code] Your code here [/code]

  4. #4
    Board Regular
    Join Date
    Mar 2009
    Location
    St. Louis, MO
    Posts
    1,561

    Default Re: Need Formula to Convert Date/Timestamp to Month-Year

    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")
    Never stop learning

    Excel 2013 Windows 7 (work)
    Excel 2007 Windows XP (home)

  5. #5
    New Member
    Join Date
    Oct 2009
    Posts
    2

    Default Re: Need Formula to Convert Date/Timestamp to Month-Year

    It worked. Thanks Barry!!!

  6. #6
    Board Regular Domski's Avatar
    Join Date
    Jan 2005
    Location
    Leeds, UK
    Posts
    7,178

    Default Re: Need Formula to Convert Date/Timestamp to Month-Year

    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
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    - Guidelines For Posting

    - Mr Excel Articles and PodCasts

    - Display sheet using HTML Maker or Excel Jeanie

    - Something that makes me laugh

    - Please try to remember to use code tags when posting your VBA code: [code] Your code here [/code]

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com