Sorting and Date Format Help

[PHisaw]

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

 

[steve]

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]

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

 

[Helmut Meukel]

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

How about:
in VBA
Function CalcDate(byval actDate as date) as date
CalcDate = DateSerial(Year(actDate),Month(actDate),1)
End Function

or in Excel, assuming Class is in column A, TheDate is in column C.
Format CalcDate (column B) as mmm-yy and enter this formula into B2
=DATE(YEAR(C2), MONTH(C2),1)

BTW, any day value will do if it's below 29 and always the same.
(29 and 30 will cause February dates displayed as March, and
31 would create wrong months for all month with only 30 days)

If you want April dates show CalculatedDates of July:
=DATE(YEAR(C2), MONTH(C2) + 3, 1)

Helmut.