asked on

Excel Date Formatting

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:

6/2012
7/12/2012
8/2012
8/13/2012

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.

SMAHelpDesk

ASKER

So this would be helpful to get some advice.

ASKER CERTIFIED SOLUTION

Kent Dyer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

p_nuts

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

RGRodgers

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.

...RG

Steve

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.

RGRodgers

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.

...RG