Sorting and Date Format Help
Steve,
Thanks for replying. When I came back here to post that I had solved the
problem, I saw where you responded. I ran across this formula
"=A2+1-Day(A2)" that returns the date of 6/12/10 as 6/1/10 and then I can
change the cell format to mmm-yy and it works as needed.
Thanks for your help.
Pam
"steve" wrote:
By formating the date you only affect the way the date is displayed in a
cell so the day part is still there.
Rather than having the Calculated date as you have used, create 2 new
calculated columns. If your date is in columnA
B1=month(A1)
C1=year(A1)
Copy/fill these down, then use these two as your sort criteria
These just return a number and so can be used to sort.
Regards
Steve
"PHisaw" wrote in message
...
I hope this makes sense, but without going into a lengthy detail of what I
need to have work, is there a way to take a date and either use said date,
or
add either 30 or 90 days to it and format it as mmm-yy and still be able
to
sort and group it without the interference of the day portion of the date?
I
have the formula for the calculated date working as needed. Now (I think)
it
is a formatting problem with the date.
I need to sort a second field and the day portion is causing it to not
sort
as needed.
Example:
Class CalculatedDate Shown in Formula Bar
AKU Apr-10 4/1/10
MMU Apr-10 4/5/10
TKU Apr-10 4/7/10
AKU Apr-10 4/9/10
I sort by CalcuatedDate and then by Class, but it's actually sorting by
the
day in the CalcDate which is correct, but is there a way around it. I
need
all Apr-10 grouped together regardless of day and then Class sorted and
grouped alphabetically.
I've spent a considerable amount of time on this and it is for automation
for a project that occurs weekly, so any help would certainly be
appreciated.
Thanks in advance.
Pam
.
|