Velocity Reviews > Date sorting in excel

# Date sorting in excel

Craig Shore
Guest
Posts: n/a

 12-20-2005
Just wondering if this is possible.

In a column full of dates with date, month and year, we want to sort it by the
month. Even when we've only got the month and date visible (by formatting the
cells) it still seems to sort it on the year first.

Is it possible, and if so how?

TIA.

Armpit
Guest
Posts: n/a

 12-20-2005

"Craig Shore" <(E-Mail Removed)> wrote in message
news(E-Mail Removed)...
> Just wondering if this is possible.
>
> In a column full of dates with date, month and year, we want to sort it by
> the
> month. Even when we've only got the month and date visible (by formatting
> the
> cells) it still seems to sort it on the year first.
>
> Is it possible, and if so how?
>
> TIA.

Dunno if it is that way, but you could always make a second (hidden) column
with a datepart bit that only has the month, or month + day, and sort that
way.

Alan
Guest
Posts: n/a

 12-21-2005
"Craig Shore" <(E-Mail Removed)> wrote in message
news(E-Mail Removed)
> Just wondering if this is possible.
>
> In a column full of dates with date, month and year, we want to sort
> it by the month. Even when we've only got the month and date
> visible (by formatting the cells) it still seems to sort it on the
> year first.
>
> Is it possible, and if so how?
>
> TIA.
>

Hi Craig,

I don't think you will be able to do that, since as far as excel is
concerned, the dates are actually values (probably around 38,000)
which is the number of days since 1 Jan 1900.

When you sort, you are really sorting a list of numbers.

To sort by month, you could add a helper column that pulls out the
month only and then sort by that column.

If your dates are in A2:A99 then put the following in a new column:

=Text(A1,"MM")

That will return a two digit string representing the month (e.g. Dec =
"12") which you could then use to sort.

Once sorted, you could hide that column or delete it altogether if you
want.

HTH,

Alan.

--
The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

http://www.velocityreviews.com/forums/(E-Mail Removed)

This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb

Peter
Guest
Posts: n/a

 12-21-2005
Alan wrote:
> If your dates are in A2:A99 then put the following in a new column:
> =Text(A1,"MM")

Or in the new column, use
=Month(A2)
and copy this down
then sort on this new column.

Depending how you want to process the data, you might find using a pivot
table worthwhile.

HTH

Peter

Craig Shore
Guest
Posts: n/a

 12-21-2005
On Wed, 21 Dec 2005 19:02:35 +1300, Peter <(E-Mail Removed)> wrote:

>Alan wrote:
>> If your dates are in A2:A99 then put the following in a new column:
>> =Text(A1,"MM")

>
>Or in the new column, use
> =Month(A2)
>and copy this down
>then sort on this new column.
>
>Depending how you want to process the data, you might find using a pivot
>table worthwhile.

Thanks guys, will give it a go tomorrow. At work they send out birthday cards
and a voucher for a free muffin to all staff. They were maintaining a seperate
database of this information, but it's too time consuming to keep it up to date
with new staff and who has left. They were looking at dropping it, but decided
perhaps they could export the data out of the payroll and use that requiring
minimal work to get the information. But they struck the above problem. My
incentive to find an answer - I still get my free muffin next year now
Actually the real motivation was I thought there must be a way and it was
bugging me not being able to do it when they asked me if it was possible.

Thanks.

Alan
Guest
Posts: n/a

 12-22-2005
"Craig Shore" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)
>
>Actually the real motivation was I thought there
> must be a way and it was bugging me not being able to do it when

they
> asked me if it was possible.
>
> Thanks.

I usually find that, when the question is along the lines of, "Can
Excel do ... XXXX" then answer is nearly always "Yes" (unless the one
asking the question seriously off the planet).

However, a better question (or a good follow up one) is:

"Should we do this in excel or use a different tool?"

The answer to that is often that excel is not the best tool for the
job even though it can be bent fit the requirements.

Alan.

--
The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

(E-Mail Removed)

This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb