Chronological Dates

nancybrown

Well-known Member
Joined
Apr 7, 2005
Messages
868
:confused: Hi Guys,

I have a report the has date formats of Jan 05, Feb 05, etc., Can someone tell me how to put these months in chronological order.

Another issue: Is it possible to take data on a subform datasheet view and format it as a string, e.g.:

Currently Would like: Monday, Tuesday, Wednesday
Monday
Tuesday
Wednesday

Kindest Regards,
Nancy
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
Nancy

Are these dates actually in a date field or a text field?
 

nancybrown

Well-known Member
Joined
Apr 7, 2005
Messages
868
Hi,

They're currently in a date field. I modified the format in the query to display as Jan, Feb, etc. Unfortunately I don't want them to sort ascending or descending, but Chronologically.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
nancy

The format of a field shouldn't affect how it's sorted.

How did you actually do the formatting?
 

nancybrown

Well-known Member
Joined
Apr 7, 2005
Messages
868

ADVERTISEMENT

Hi,


This is the code I used in the query field:

Month: Format([StartDate],"mmm yy")
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
Well by using Format you've changed the date into text, therefore it will be sorted alpabetically.

Where are you actually using the query?

Is it for a report?
 

nancybrown

Well-known Member
Joined
Apr 7, 2005
Messages
868

ADVERTISEMENT

Yes in a yearly financial report that is grouped by month.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
Well instead of using the Format function, format the date using the Format property.

Just select the date field, goto the Format tab and enter mmm yy.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,032
Messages
5,569,764
Members
412,291
Latest member
marypolitan
Top