Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel Date Formatting

Posted on 2012-09-01
6
Medium Priority
?
367 Views
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:


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.
0
Comment
Question by:SMAHelpDesk
6 Comments
 

Author Comment

by:SMAHelpDesk
ID: 38357773
So this would be helpful to get some advice.
0
 
LVL 17

Accepted Solution

by:
Kent Dyer earned 2000 total points
ID: 38357789
You are going to have to write custom code:
Month(DateString) & "/" & Year(DateString)

something along those lines should do it.

HTH,

Kent
0
 
LVL 13

Expert Comment

by:p_nuts
ID: 38357796
I would use a different column and use that for the sorting and you can manipulate that anyway you like
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:RGRodgers
ID: 38357857
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
0
 
LVL 24

Expert Comment

by:Steve
ID: 38359036
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.
0
 
LVL 8

Expert Comment

by:RGRodgers
ID: 38359078
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
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
Gain an elementary understanding of Blockchain technology.
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
Integration Management Part 2

571 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