+ Reply to Thread
Results 1 to 7 of 7

mm/dd/YYYY to just YYYY? Formula?

  1. #1
    Registered User
    Join Date
    12-08-2006
    Posts
    3

    mm/dd/YYYY to just YYYY? Formula?

    Something tells me this may be simple, but who knows. I've got a sheet with dates listed in a column as mm/dd/YYYY OR YYYY format, but need those dates to be constrained to just the YYYY portion. Is there a formula I can use for this? I know excel doesn't natively include just a YYYY format.

    Thanks in advance!

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Hi,

    Select Column then Format > cells > Custom YYYY

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Registered User
    Join Date
    11-20-2006
    Posts
    23
    Format, on the top menu
    Cells...on the drop down menu
    Choose the Number tab, and then choose 'Custom'
    Click on a choice in the list that is a date, and then type into the "type:" box:- YYYY
    Click:- OK

    When you enter any date in that cell it will only display the YYYY year.


    Dfire

  4. #4
    Registered User
    Join Date
    12-08-2006
    Posts
    3
    This only works for those numbers that are in a format other than YYYY. If I have a row of mixed mm/dd/YYYY and YYYY numbers, all the YYYY get turned to "1905" for some reason using the above suggestions. Any others?

    Thanks

  5. #5
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Here's one way.

    Use a helper column. Say dates in Col F . Enter this in Col G and drag down. Then paste special values and format as YYYY

    =IF(ISERROR(DATE(VALUE(F2),1,1)),F2,DATE(VALUE(F2),1,1))

    VBA Noob

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Quote Originally Posted by rkmase
    This only works for those numbers that are in a format other than YYYY. If I have a row of mixed mm/dd/YYYY and YYYY numbers, all the YYYY get turned to "1905" for some reason using the above suggestions. Any others?

    Thanks
    If you format 2006 as YYYY you get 1905 because Excel starts coutning dates at 1st January 1900 so 2000 days after that date is within 1905....

    anyway....if your "dates" are in A2 down try this formula in B2 copied down, to give the year only, whether A2 contains just the year - 2006 - or a date 12/12/2006

    =IF(A2<3000,A2,YEAR(A2))

    format as general

  7. #7
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Nice one Daddylonglegs as ever

    VBA Noob

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1