Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Location
    Wausau, Wisconsin, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Format w/o year (Excel 2000 9.0.3821 SR-1)

    I'm looking for a way to have Excel display dd/yy without also assigning the current year to the cell. Creating a Custom Format doesn't do the trick and the Excel help points to the Regional Settings to change the short date format. What am I missing?

    We keep track of dates (minus year) and need to sort in ascending order. With Excel assigning a year, it makes it impossible! Thanks for your help! ~Tammy

    Any help is appreciated.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Date Format w/o year (Excel 2000 9.0.3821 SR-1)

    Excel dates are a count of days and fractions of days elapsed from and including 1/1/1900, and definitionally include years. Would you be OK creating a separate column to sort on? Assuming your date is in cell A1, in B1 enter =A1-DATE(YEAR(A1),1,0), format it as general and copy down. This formula gives you the day in the year of any year Excel can handle, and can be sorted on. (I keep thinking that there's a day-of-year function but don't see it.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Format w/o year (Excel 2000 9.0.3821 SR-1)

    I'm not sure what your problem is. Why would you want to display dd/yy? If you aren't entering the year, why do you want to display it? Also, why the day of the month and the year without the month? I don't know useful information anyone could get out of that. Are you by chance wanting to display a Julian date (ddd/yy - day of the year and year)? And if so, what do you want the year to be if you are not entering it and Excel's default of the current year is not acceptable.
    Legare Coleman

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date Format w/o year (Excel 2000 9.0.3821 SR-1)

    Do you mean display dd/mm rather than dd/yy ?.

    Also it is not clear in what order you want them sorted, but if you want the sort to run from 1/1/ to 31/12/, ignoring the year, your best be might be toi include a new column with the following formula :

    =(TEXT(A1,"mmdd")), replacing A1 with the cell that actually contains the date

    and use that column for sorting.

    Andrew C

  5. #5
    New Lounger
    Join Date
    Jun 2002
    Location
    Wausau, Wisconsin, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Format w/o year (Excel 2000 9.0.3821 SR-1)

    Sorry...I fat-fingered the format...I'm looking for mm/dd. Reasoning: We keep track of and publish a birthdate list organization-wide. We cannot and will not publish year, just month and day. It is very helpful to be able to sort the list according to mm/dd.

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Date Format w/o year (Excel 2000 9.0.3821 SR-1)

    If your data hasn't already been entered, simply type a single quote (') before inputting the date (eg '03-11). That tricks Excel into thinking it's dealing with text and your sorting will work as needed.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    New Lounger
    Join Date
    Jun 2002
    Location
    Wausau, Wisconsin, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Format w/o year (Excel 2000 9.0.3821 SR-1)

    Simply brilliant! It's amazing how easy it is to overlook the obvious...thanks a mil!

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Format w/o year (Excel 2000 9.0.3821 SR-1)

    Using that method can cause some problems. For example, if someone enters '3-11 it will not sort correctly. If it were me, I would just always enter the year as 00 and then use a format that only displayed the month and day.
    Legare Coleman

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Date Format w/o year (Excel 2000 9.0.3821 SR-1)

    I don't want to argue with a solution that works for you, but in the US, birthdates are a critical part of Human Resource records, so I'm a bit surprised you want to create this file without using "real" dates. Should you need to use full dates in the future, you won't be able to source them from this file. OK, I'm sorry, I guess I am arguing with the solution, no malicious intent on my part. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date Format w/o year (Excel 2000 9.0.3821 SR-1)

    <P ID="edit" class=small>Edited by DoryO on 19-Jun-02 16:38.</P>I have to agree with John. As soon as someone utters "we don't need those entered as dates, text is fine" someone asks for a new report by date. Also, your users won't enter something goofy like "06-31" which is not a valid date. :-)

    How about a sorting column filled with this formula (if Col A has the true dates):

    =TEXT(A1,"mm-dd")

    This returns text you can sort by regardless of year.

    {Edit: I just noticed a previous post with similar suggestion. Sorry to repeat it. But, seriously, Tammy -- avoid that leading-apostrophe method of entering values as text. It seduces so many.}
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  11. #11
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Date Format w/o year (Excel 2000 9.0.3821 SR-1)

    The formula-driven approach is fine if "publishing the list organisation-wide" as Tammy says is the intention doesn't involve distributing the spreadsheet ... since no matter what you do any savvy user would still be able to extract the years.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  12. #12
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date Format w/o year (Excel 2000 9.0.3821 SR-1)

    Sorry. It gives me the heebie-jeebies to think that the DOBs are already available but will be retyped -- as text. Whatever. As long as Tammy's happy and find it a brilliant solution, that's what counts!

    (The old Copy...PasteSpecial...Values trick would let you remove the Date of Birth column after calculating the "yearless Birthday" text, if necessary.)
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  13. #13
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Date Format w/o year (Excel 2000 9.0.3821 SR-1)

    That's OK,
    just that it didn't look like a personnel/HR issue to me, but more like something to do with producing a birthday list for social purposes. If the birthdays are being garnered from a database or spreadsheet that has the birthdates, using your formula followed by Copy...PasteSpecial...Values would be ideal.
    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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