Windows Vista Forums

Excel leading zero for day and month

  1. 22 Mar 2012 #1
    Frank C's Avatar
    Frank C

    Member
    Join Date : Jun 2009
    Potomac, MD
    Posts : 140
    Vista Ultimate x64 SP2
    Local Time: 01:58 AM
    usa us maryland

    Excel leading zero for day and month

    Hi
    In Excel, I want to get a leading zero on a numeric day and month entries for sort.
    I have separate columns for year, month and day.
    I have tried many formats without success. If I format as text with a leading zero I get a warning that a number is stored as text and the sort is incorrect.
    Does anyone have a suggestion?
    Thanks
    Frank C


      My System SpecsSystem Spec
    Reply With Quote Reply With Quote
  2. 23 Mar 2012 #2
    Lottiemansion's Avatar
    Lottiemansion

    Senior Member



    Join Date : Sep 2010
    Sheffield
    Posts : 1,820
    Vista Home premium 32 bit (SP2 plus)
    Local Time: 06:58 AM
    uk uk yorkshire

     

    Re: Excel leading zero for day and month

    Hi,

    You can use a custom format & from the dd/mm/yyyy format use only the dd part.

    When you insert a date you will get the first two digits for the day. It is the same for a month (mm) & year (yyyy) or (yy)

    i.e 23/03/2012 = 23
    or 01/03/2012 = 01

    OK?

    Last edited by Lottiemansion; 23 Mar 2012 at 11:42 AM.
      My System SpecsSystem Spec
    Reply With Quote Reply With Quote
  3. 23 Mar 2012 #3
    Frank C's Avatar
    Frank C

    Member
    Join Date : Jun 2009
    Potomac, MD
    Posts : 140
    Vista Ultimate x64 SP2
    Local Time: 01:58 AM
    usa us maryland

      Thread Starter

    Re: Excel leading zero for day and month

    Thanks Lottie
    I tried your suggestion with no success. I don't understand the custom approach. I also tried formating the date in one column as dd/mm/yyyy. That did not sort properly either.
    Frank C

      My System SpecsSystem Spec
    Reply With Quote Reply With Quote
  4. 23 Mar 2012 #4
    Lottiemansion's Avatar
    Lottiemansion

    Senior Member



    Join Date : Sep 2010
    Sheffield
    Posts : 1,820
    Vista Home premium 32 bit (SP2 plus)
    Local Time: 06:58 AM
    uk uk yorkshire

     

    Re: Excel leading zero for day and month

    Hi,

    Please post a picture of the excel spread sheet & explain the cell contents & cell formating.

    If possible/ appropriate post the file & I will look at it. This will enable me to advise further. OK?

    See if these files help you to solve the problem? Please watch the video first!

    Last edited by Lottiemansion; 23 Mar 2012 at 02:22 PM. Reason: Added sample files
      My System SpecsSystem Spec
    Reply With Quote Reply With Quote
  5. 24 Mar 2012 #5
    Frank C's Avatar
    Frank C

    Member
    Join Date : Jun 2009
    Potomac, MD
    Posts : 140
    Vista Ultimate x64 SP2
    Local Time: 01:58 AM
    usa us maryland

      Thread Starter

    Re: Excel leading zero for day and month

    Thanks Lottie,
    I reluctantly changed to English US format MM/DD/YYYY in one column that meant that I had to transpose the month and day. That column DID sort properly. No leading zeros on month or day I will live with that solution.
    When I get some time I will try the English Canadian format with the entire date in one column (YYYY-MM-DD) this makes more sense to me than the English US format.
    Frank C

      My System SpecsSystem Spec
    Reply With Quote Reply With Quote
  6. 24 Mar 2012 #6
    Lottiemansion's Avatar
    Lottiemansion

    Senior Member



    Join Date : Sep 2010
    Sheffield
    Posts : 1,820
    Vista Home premium 32 bit (SP2 plus)
    Local Time: 06:58 AM
    uk uk yorkshire

     

    Re: Excel leading zero for day and month

    OK,

    Glad you have made some progress & I wish you well in your ventures.

    Paul H.

      My System SpecsSystem Spec
    Reply With Quote Reply With Quote
Excel leading zero for day and month
« Excel macro to fetch details from xml file | In Excel 2007, how do I enbed a hyperlink? »

Similar Threads
Thread Thread Starter Forum Replies Last Post
Browser Power Consumption—Leading the Industry with Internet Explorer 9 SGT Oddball Vista News 0 28 Mar 2011
Re: Leading comma operator - What does it do, really? Marco Shaw [MVP] PowerShell 3 15 Sep 2009
Leading Mattress-Maker Spring Air to Rest Easier With Microsoft Dynamics AX Technology Solution z3r010 Vista News 0 06 Jul 2006