Excel Date Formatting

Posted on 2012-09-01
Last Modified: 2012-12-04
If you have dates in one column that have month/day/year and others have month/year , is there a way to sort that column without having Excel input the number 1 as they day for the dates that do not specify a day. I would like to sort them such as:


and etc.

without Excel automatically adding a 1 to the dates that are just 6/2012. I don't want it to look like 6/1/2012.
Question by:SMAHelpDesk

    Author Comment

    So this would be helpful to get some advice.
    LVL 17

    Accepted Solution

    You are going to have to write custom code:
    Month(DateString) & "/" & Year(DateString)

    something along those lines should do it.


    LVL 13

    Expert Comment

    I would use a different column and use that for the sorting and you can manipulate that anyway you like
    LVL 8

    Expert Comment

    Building from Kent:

    IF(DAY(DateString)=1,(MONTH(DateString) & "/" & YEAR(DateString)),(MONTH(DateString) & "/" & DAY(DateString) & "/" & YEAR(DateString)))

    The cell containing the date should be named DateString in this case.

    LVL 24

    Expert Comment

    If you have dates in one column that have month/day/year and others have month/year

    If you have '8/2012' and '7/12/2012' then you do not have dates in one column you have text.
    This will sort as text unless you convert them to dates. Dates in excel are funky 5 didgit numbers formatted to look like dates.

    If you don't want it to look like 6/1/2012 then you can custom format to m/yyyy and it will look like 6/2012, but still be a date and sort correctly.
    LVL 8

    Expert Comment

    All true, but I figured they'd sort in the column containing the real dates.  The problem with using specific formatting like m/yyyy is that you would have to format each cell as desired.  Yeah, there is probably an app for that too.

    I reserved my comment that I didn't see the reasoning behind eliminating the "day" just because it happened to be a 1.  What was important was simply answering the question.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Article by: Leon
    Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
    Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now