Sorting dates in Excel

[Earl Kiosterud]

You said you wanted an alphameric sort, but that would be according to how they're formatted
(Feb 1, 2007, 2/1/07, etc). I don't think that's what you want.

It sounds as though you have true dates, and are getting a true date sort, but want them
sorted by month, not actual date. February 2007 would sort behind December 2006.

If so, set up another column (you can hide it when you don't need it). =MONTH(A2) where the
dates are in column A. Sort that column.

 

[KC Rippstein]

I might suggest separating the month and year in your worksheet. If your
dates are numeric and stored in column B, then:
- C1 is "Month", C2 =month(B2)
- D1 is "Year", D2 =year(B2)
Copy those formulas down as far as needed. Then highlight columns A and
sort by "Year" descending, then "Month" descending.

You can also use formulae to automatically track people's accrual based on
their hire date instead of doing it manually. Post back more details on how
you're doing that if you want that task automated.

-KC

 

[L]

It should be easy.. but I just can't figure this out.

My employees vacation and sick time are accrued bi-annually according to
their hire date. I want to create a list that will help me manually update
vacation and sick time, a list sorted by the month and day the employees
were hired.

I can export a list of employees and hire dates to excel. I want to sort the
list by hire date, treating the hire date alphanumerically for the sort. In
other words, I want to sort a hire date of February 10, 2006 (02/10/2006)
BEFORE a hire date of December 14, 2001 (12/14/2001).

I've tried to change the format of the hire date cells to general, or text
(Excel fills the cells with strange values) -- I have even tried to format
as a date WITHOUT the year (but the excel option for that writes dates
without the preceding zeros, and so February still sorts behind December).

It shouldn't be that hard to treat numbers as text. I've tried searching
help, but I'm just not finding what I need.

Is there anyone here willing to help?

 

[L]

You said you wanted an alphameric sort, but that would be according to how
they're formatted (Feb 1, 2007, 2/1/07, etc). I don't think that's what
you want.

Actually, yes, I do. The dates are already formatted with starting zeros
(02/01/2007).

It sounds as though you have true dates, and are getting a true date sort,
but want them sorted by month, not actual date. February 2007 would sort
behind December 2006.

If so, set up another column (you can hide it when you don't need it).
=MONTH(A2) where the dates are in column A. Sort that column.

That worked... sorta.
I honestly expected a two digit result. Instead, excel returned a value of
01/xx/1900, where xx is the two digit month I wanted.

And I still have some 'hand sorting' to do, in months where multiple
employees were hired, as I wanted the list sorted by month/day.

Is there really no way to have excel treat column values as alphanumeric? It
seems such a basic request!

 

[David McRitchie]

To create the text equivalent
B1: =text(A1,"mm/dd/yyyy")

will test as True for =ISTEXT(B1)

 

[David McRitchie]

Just ran across almost the same question in another
newsgroup, guess it was just a coincidence; however
subject to the same initial misunderstanding of the question.

http://google.com/groups?threadm=4AC1EEB7-7DD3-4675-AC17-B2F371240EF1@microsoft.com

 

[Earl Kiosterud]

L,

OK. It's working. The weird date you're getting is the inappropriate date formatting that
it applied. Set it to General (Format - Cells - Number tab). You should now see 1 for any
January date, 2 for any february date, etc. If you want the dates within each month in
sequence, try =DATE(0,MONTH(A2),DAY(A2)).

I don't see how an alphameric sort would be the solution you need.

 

[L]

I might suggest separating the month and year in your worksheet. If your
dates are numeric and stored in column B, then:
- C1 is "Month", C2 =month(B2)
- D1 is "Year", D2 =year(B2)
Copy those formulas down as far as needed. Then highlight columns A and
sort by "Year" descending, then "Month" descending.

You can also use formulae to automatically track people's accrual based on
their hire date instead of doing it manually. Post back more details on
how you're doing that if you want that task automated.

I would *LOVE* to automate that task. I've put it off because of the
learning curve.

The details are:
Employees have one week (40 hours) of sick pay and at least one week (more
for longer service) of vacation time per year.

The hours for sick and vacation are accrued bi-annually. So, if a person was
hired on 2/14/07 they are entitled to half their annual sick/vacation on
8/14/07, and the balance on 2/14/08. There are no fixed expiration dates (as
in end-of-year) but there are maximum limits - no person can have more than
5 days of sick time or more than 1 1/2 years of vacation available at any
time.

I would appreciate any help in automating this task. The software we use for
payroll and accounting has only a 'per paycheck' or 'on anniversary date'
accrual - and keeps only running totals of paid time off used (on a calendar
year basis) - so at update time I run paid time off reports that go back to
date of hire, in order to see what was used and what is available.

Thanks so much for all your help.

 

[L]

L,

OK. It's working. The weird date you're getting is the inappropriate
date formatting that it applied. Set it to General (Format - Cells -
Number tab). You should now see 1 for any January date, 2 for any
february date, etc. If you want the dates within each month in sequence,
try =DATE(0,MONTH(A2),DAY(A2)).

When I get back to the office, I will give that a try.

I don't see how an alphameric sort would be the solution you need.

If excel treated the dates in the format of MM/DD/YYYY as text, the year
would be irrelevant. So long as February, for example, had a value of '02'
as opposed to '2', it would sort BEFORE October, November, and December '10,
11, 12'

Of course, I am old enough to remember that a character space has a value of
hex 40... and I can see the humor in the internet signature "there are 10
types of people in this world: those who understand binary and those who
don't." Sorting data by the blanket numeric values of the characters within,
actually makes sense to me!

 

[KC Rippstein]

On second thought, it sounds like you are not using Excel to track leave and
accruals. You're using Access, right? If you like, you can just email me
direct and we'll work out a solution for you. I'm happy to help but would
like to see what you are actually working with now before making
recommendations. Setting up something should not take too long, but I am
unclear on the best approach until we clear up what your current setup is.
-KC

 

[Earl Kiosterud]

L,

Now I see what you mean. Excel doesn't sort on the date the way it's formatted. It sorts
on the actual underlying date value. Let us know how the formula method works.

 

[L]

On second thought, it sounds like you are not using Excel to track leave
and accruals. You're using Access, right? If you like, you can just
email me direct and we'll work out a solution for you. I'm happy to help
but would like to see what you are actually working with now before making
recommendations. Setting up something should not take too long, but I am
unclear on the best approach until we clear up what your current setup is.

No, I am not using Access.

Employee data is contained within the accounting software package (we use
QuickBooks).
Data can be exported to Excel for manipulation - or, if one is ambitious,
QuickBooks supplies an Software Development Kit that lets you access the
data more directly.

The formula my company uses to accrue sick/vacation time is not one of the
options in the QuickBooks payroll module (an extra). So, I exported the
results from one of the supplied reports - an employee contact listing -
modifying the report to exclude unnecessary information and include the hire
date.

Right now, I printed the spreadsheet (had to do some cut and paste to get
the employee names in order, as I could not sort by month and day). I am
manually entering how many vacation/sick hours have accrued, and how many
have been used to date.

Unless I can figure out how to automate the bi-annual accrual using the hire
dates, I will just have to slap the sheet on the wall and do a manual update
within QuickBooks by checking the list against the calendar and updating
each time an employee date comes due. Of course, since each employee accrues
twice a year, I'll have to make a second list with the bi-annual dates, sort
on that, and merge the two.......

Forty six line entries for 23 employees.

Sigh

 

[KC Rippstein]

I emailed you a file that should automate this for you. Let me know if you
did not rcv or have ?s.
KC

 

[KC Rippstein]

Have not heard back from you. Did you receive the spreadsheet? Does it
meet your needs?
-KC