"PHisaw" schrieb im Newsbeitrag
...
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.