LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 49
Default 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





 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
permanent conversion of 1904 date format to 1900 date format Jos Excel Worksheet Functions 4 November 26th 15 02:48 PM
change date format dd/mm/yyyy to Julian date format? itzy bitzy Excel Worksheet Functions 1 December 8th 09 07:42 PM
convert serial date format to normal date format Flagworld Excel Discussion (Misc queries) 3 September 23rd 08 01:32 PM
code to convert date from TEXT format (03-02) to DATE format (200203) Gauthier[_2_] Excel Programming 0 September 22nd 04 03:26 PM
Change a date in text format xx.xx.20xx to a recognised date format concatenator Excel Programming 1 November 24th 03 11:33 PM


All times are GMT +1. The time now is 12:07 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"