Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting and Date Format Help
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting and Date Format Help
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting and Date Format Help
"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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
permanent conversion of 1904 date format to 1900 date format | Excel Worksheet Functions | |||
change date format dd/mm/yyyy to Julian date format? | Excel Worksheet Functions | |||
convert serial date format to normal date format | Excel Discussion (Misc queries) | |||
code to convert date from TEXT format (03-02) to DATE format (200203) | Excel Programming | |||
Change a date in text format xx.xx.20xx to a recognised date format | Excel Programming |