Sort Dates by Month and Day, Not Year

Is there a trick to sort a date column by Month and Day, and not have it 
sort the list by year as first sort.  Without breaking up the data into 
multiple columns. 


0
albert3569 (90)
12/28/2008 3:07:18 AM
excel 39879 articles. 2 followers. Follow

4 Replies
431 Views

Similar Articles

[PageSpeed] 55

Hi,

You could add a column with the formula

=YEARFRAC(DATE(YEAR(A1),1,1),A1)

and sort on that column.  It does sound like you want to avoid this.

If your dates are the row field of a pivot table and your group by month and 
day Excel will ignore the year.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"Al Franz" <albert@nospam.netmation.com> wrote in message 
news:OsrEqlJaJHA.5340@TK2MSFTNGP05.phx.gbl...
> Is there a trick to sort a date column by Month and Day, and not have it 
> sort the list by year as first sort.  Without breaking up the data into 
> multiple columns.
> 
0
12/28/2008 9:17:39 AM
I'd use a formula like this in a helper column:

=text(a1,"mmdd")

Then sort the data by that helper column.

Al Franz wrote:
> 
> Is there a trick to sort a date column by Month and Day, and not have it
> sort the list by year as first sort.  Without breaking up the data into
> multiple columns.

-- 

Dave Peterson
0
petersod (12005)
12/28/2008 1:12:58 PM
Thanks Dave that helps.  Still haven't gotten my desired result, but if you 
have a minute maybe can take a look at the following....

http://netmation.com/dateprob.xls

Can see the formula I am currently using and see if there is a way to get 
the forumula to give me the desired results.  The cells in red are the 
desired results the formula is not working for.

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:49577B5A.1646BC4@verizonXSPAM.net...
> I'd use a formula like this in a helper column:
>
> =text(a1,"mmdd")
>
> Then sort the data by that helper column.
>
> Al Franz wrote:
>>
>> Is there a trick to sort a date column by Month and Day, and not have it
>> sort the list by year as first sort.  Without breaking up the data into
>> multiple columns.
>
> -- 
>
> Dave Peterson 


0
albert3569 (90)
12/29/2008 8:25:49 PM
I don't open files.

Remember that this formula only works on dates--not text.  If it's not working,
I bet your "dates" aren't dates--just plain old text.



Al Franz wrote:
> 
> Thanks Dave that helps.  Still haven't gotten my desired result, but if you
> have a minute maybe can take a look at the following....
> 
> http://netmation.com/dateprob.xls
> 
> Can see the formula I am currently using and see if there is a way to get
> the forumula to give me the desired results.  The cells in red are the
> desired results the formula is not working for.
> 
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:49577B5A.1646BC4@verizonXSPAM.net...
> > I'd use a formula like this in a helper column:
> >
> > =text(a1,"mmdd")
> >
> > Then sort the data by that helper column.
> >
> > Al Franz wrote:
> >>
> >> Is there a trick to sort a date column by Month and Day, and not have it
> >> sort the list by year as first sort.  Without breaking up the data into
> >> multiple columns.
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12005)
12/29/2008 8:48:56 PM
Reply:

Similar Artilces:

Printing a date without the year
I am new to Access, and I want to know how to display just the month and the day not the year. Is there anyway to do this Display it where? Form, Query, Report??? One option would be to use the Format(), somthing like Format(Date(),"mmmm-dd") Lookup the Format function to see all the variables available to control the output format. -- Hope this helps, Daniel Pineault http://www.cardaconsultants.com/ For Access Tips and Examples: http://www.devhut.net Please rate this post using the vote buttons if it was helpful. "C Schroder" wrote: > I...

Validate cell for date, as well as day of the week
I have looked through what is out there and I am not hopeful that this can be done, but here goes... I have a cell that will be populated with a date. I need to make sure that the date does not fall on a Saturday or a Sunday and that the date is not older than 2 days. I was hoping to be able to do both of these in a single validation, but I have a snag. The weekday function requires a cell, but I am validating on the current cell and, even though I do know what it is, i want to allow that that may change. How can I accomplish this? David I could not get data validation to work....

Dates in excel curiouser and curiouser
Hi folks, I am creating a time sheet but there are some strange problems, notably it calculates the time fine until thw total is over 24 hours and for some reason the minutes are getting rounded up despite there being 2 decimal places...eg 6 minutes becomes hh:10. Very odd and any help would be appreciated Cells are formated as hh:mm. Formulae are either a) (Start Time - End time )-(Lunch End-Lunch start) or sum((End Time - Lunch end)*24,(lunch start-start time)*24)) Many thanks, Danny Sounds like a problem with the time format. Something like hh:00 instead of hh:mm. Can you check th...

How can I count dates if few duplicates in a column
I entered few dates in Column C3 to C20, few of them are duplicates. How can I count total number of dates (excluding duplicates), duplicates should be count 1. Try the below =SUMPRODUCT(--(C3:C20<>""),1/COUNTIF(C3:C20,C3:C20&"")) -- Jacob (MVP - Excel) "Tariq Aziz" wrote: > I entered few dates in Column C3 to C20, few of them are duplicates. > How can I count total number of dates (excluding duplicates), duplicates > should be count 1. Hi, Try this =SUMPRODUCT((C3:C20<>"")/COUNTIF(C3:C20,C3:C20&am...

Find Date Mailbox Created
Hello, In ADSIEdit you can see the whenCreated field, but this refers to the date the AD account was created. Does anyone know how to find the date the account was given a mailbox? We had AD long before Exchange and I'm trying to find out when people were given mailboxes precisely. Thanks. i struggled with this, too, and I don't believe there is any way to get this info...I miss having that at my fingertips... -- Susan Conkey [MVP] "GC Postmaster" <gc_postmaster@gordon.edu> wrote in message news:9662BF63-2EAF-4D29-B318-0861643E87A1@microsoft.com... > Hello...

Calendar default date
Using excels calendar on a worksheet works great. However I would lik it to default to todays date. I would think this would be the defaul but I guess not. I have inserted an Calendar 8.0 object. I have inserted the following code in the worksheets code. Private Sub Calendar1_Click() 'ActiveCell.NumberFormat = "m/d/yyyy" ActiveCell = Calendar1.Value Calendar1.Visible = False Range("e27").Select End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 2 And Target.Row = 27 Then Range("c27").activate ActiveCell.Font.Size = 12 ...

how to insert month date year and day of week
I use Win XP Home Edition, SP2, Excel 2003 Can someone inform me how to put Month, date, year and day of the week in cell. I'd like to insert for example, 05/18/06 Wed <format><cells><number><custum> enter mm/dd/yy ddd "Sachi Noma" wrote: > I use Win XP Home Edition, SP2, Excel 2003 > > Can someone inform me how to put Month, date, year and day of the week in > cell. I'd like to insert for example, 05/18/06 Wed > > > Beautiful, thank you so much Sachi from Japan "bj" <bj@discussions.microsoft.com> wro...

Comment with date stamp 12-09-09
Hello all, I copied these macros from the "Contextures" site but I would like some help. I've already run the this macro: Sub CommentNote() Application.UserName = "Note" End Sub ...but I would like to also have a automatic time stamp in the comment as well. I can run the following macro but the comment only shows the time, not the above "Note" and you have to run the macro for the time to show. To insert a comment with the current date and time, or append the current date and time to an existing comment, use the following macro: ...

Pivot table numbers turn into dates on refresh
This mystery occurrance has happened to me before but I can't remember how to correct it. Any one with similar experience and a solution please? Visit www.ExcelGoodies.Com...

MRP Workbench Schedule Receipt Date
When using the MRP workbench, if the flag is on in MRP Preference Defaults to move in, the scheduled receipt reflects the MRP suggested date, not the current promise date on the Purchase Order or MO Due Date. The workbench makes the assumption that the exception message has been acted upon. If a Planner\Buyer used the workbench when the Move In flag is on, they will be given information that lets them believe that the component delivery schedule (PO or MO) supports the Forecast\Sales Order Demand, which is not true. The Workbench should have an option to use the PO promise date and the...

Graphing-Two stacked columns side-by-side per year
Graphing in Excel - I have six years worth of data. The 'Fiscal year' is along the x-axis, and 'Dollars' is on the y-axis. There are two sets of data (with three dollar amounts)for each year. The first is for the corporation overall, and the second is for one business unit within the corporation. I want to display two stacked columns side-by-side within each fiscal year. How do I do this? Excel keeps trying to stack the six into one column per fiscal year. Here is a depiction of what I am trying to do: A X A X A X A X B Y B Y ...

Date Increase
I currently have a form for entering current passwords for systems. I have a field to enter the date the password was last changed, and another field for the date the password expires, which is every 30 days. Is there anyway i can set the date password expires field to update automatically? The 2nd field is just calculated from the 1st and better left as a calculation in a query rather than a field in a table. There are no triggers at the table level in Access. Dan Wood wrote: >I currently have a form for entering current passwords for systems. I have a >field to enter...

Do not show $ per week when days remain < 7
I have a spreadsheet that calculates the amount of money remaining per day for the month($remain/daysremain) and per week ($remain per day * 7) for the month. But the per week logic fails when there are less than seven days remaining in the month. How can I teach excel to use $remain when daysremain < 7? A1: $ Remaining B1: Days Remaining in Month C1: =A1/B1 ($ Remaining/Day) D1: =IF(B1<7,A1,C1*7) HTH Jason Atlanta, GA "Walterius" wrote: > I have a spreadsheet that calculates the amount of money remaining per day > for the month($remain/daysremain) and per w...

How can i add a print-and save-date field in Excel as in Word?
In word, you can add a Field of the print-date and save-date. I want to do the same on an Excel document ...

Password Expires in the middle of the day
Hi, When our GP users have a password expire in the middle of the day, they get unexpected issues and some tasks don't complete properly. Is there a way to force passwords to expire at a certain time, like midnight, so that they have to change them first thing in the morning? Thank you. I think this is inherited from your domain rules and I don't know if there is a rule for that. I have found that it happens always during the day because that's when the users change their password and the time countdown starts from there. So if I change mine at 11:53 am and it expires 30...

Summing daily values by calendar month
I have a series of daily date readings(over 3 years) and would like to sum those by calendar month and year. The table is: Date Volume 1/1/07 100 1/2/07 200 I prefer to see twelve monthly values as follows... Month Volume Jan-07 300 On Wed, 26 Dec 2007 20:59:02 -0800, Adrian1962 <Adrian1962@discussions.microsoft.com> wrote: >I have a series of daily date readings(over 3 years) and would like to sum >those by calendar month and year. > >The table is: > >Date Volume >1/1/07 100 >1/2/07 200 > >I prefer to see twelve monthly values as foll...

Sorting Data Inside of Cell
New to Excel, but have a problem that I need to resolve. I'm pullin data from a web site (single table) into a spreadsheet. Everything t this point is perfect. The problem is that I need to sort the dat within each cell. Example; the number in the cell might read 275510. need to sort the data into 3 groups, 27,55,10. Does anyone have an ideas. Again I'm new to Excel but I tend to catch on quick. Thank yo in advance -- Message posted from http://www.ExcelForum.com This will take care of the data as the example that you posted. If your data starts in A1, enter this in an adjacent c...

Using SUMIF with dates
Right now, I have a database in Sheet 1 by dates listed as 1/1/09, 2/15/09, etc in column A and corresponding data in column B. Then I have a table on Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I would like to put in a SUMIF function where I can get totals for column B on sheet 1 by month IF the date in Sheet 1 column A equals the month listed in the column A in sheet 2. Right now, the only way I can do this is by creating a column C in sheet 1 and having the date in column A turned into a month by using =TEXT(A1,"mmm"). Then in sheet 2, I use t...

sum-if & sorting
My file has several sum if formulas. It seems that the formulas change themselves (the field scope ?? not sure that's the right word..) but I use the dollar sign & copy down. It works great. But during the time I use the file & sort & resort it the formula changes itself. Is there a way to keep this from happening?? Mark Can you give us an example of the formula? If the boundary edge of your range is within the sorted data, it can cause problems. Example: Formula is =SUMIF(A$4:A$7,A$9) but your sort range A2:A10. The cells your were looking at have now...

calculating no of days
How to set the conditional formating such that if current cell which has a date format is at least 14 days from system date, then the cell become black in foreground in Visual basic? Hi use the formula =A1>=TODAY()+14 -- Regards Frank Kabel Frankfurt, Germany crapit wrote: > How to set the conditional formating such that if current cell which > has a date format is at least 14 days from system date, then the cell > become black in foreground in Visual basic? One way, if I understand you correctly: Private Sub Worksheet_Calculate() Dim rCell As Range ...

Java for adding date to memo after picklist onchange
I was wondering if anyone could help me out... I have picklist values being captured via onochange on memo field. I'd like to add today's date behind each value, but I do not know java. This is what I have so far to capture which marketing material was sent: var strmaterialslist;var strmaterialsent; strmaterialslist=crmForm.CFPmaterialslist.value; strmaterialsent=crmForm.CFMmaterialsent.value; crmForm.CFMmaterialsent.value=strmaterialsent+", "+strmater ialslist As always, your help is much appreciated! Thanks, Kat var d = new Date(); defaults to now. Stephen ...

Access 2000 converting to US date format once entered
Whenever I enter a date (UK format) in Access such as 31/08/2003, it changes to 08/31/2003. I have Office 2000 installed on a Windows 2000 PC. The regional settings are set to English (United Kingdom) with default values and the Keyboard Local is also set to English (UK). Additionally, I have checked the Langauage setting for Office 2000 and it is set to English (UK). I would appreciate if anyone can help resolve this problem as there doesn't appear to be a solution on the MS Knowledge base. Thanks Joe Russell ...

need a query to get the values corresponding to latest dates
Hello everyone, Briefly stated, I have a table structured like: VariableName (string) Value (double) Date (date) Variable names can be repeated (don't worry, in the actual table I have index numbers that call up the variable names from another table), so this table contains a limited number of variables' values that can change independently. I am using two queries that work together to give the values corresponding to the latest dates. One query groups by VariableName's and uses Max(Date) aggregate function to get the latest date, then the second query joins the first query...

Defaulting History View to All instead of Last 30 Days
CRM 3.0. From Contacts or Accounts, if users want to see History, the Closed Activity Associated View defaults to filter on Last 30 Days. How would I change the Closed Activity Associated View default filter to All? Thanks, Boolean1 On Aug 24, 9:07 pm, "Boolean1" <Boole...@verizon.net> wrote: > CRM 3.0. > > From Contacts or Accounts, if users want to see History, the Closed Activ= ity > Associated View defaults to filter on Last 30 Days. How would I change = the > Closed Activity Associated View default filter to All? > > Thanks, > > Boolean1...

tri sort a list
hello how to not take account of the blank lines in a descending sort, or how to leave "the blank lines" in bottom of the sorting and not in top of the sorted columns? thanks ...