# Query: Sort Date Question

#### bsneils

##### New Member
I have a query which sorts by date, project number, work code, and sums the hours. The work date looks like

DateWorked By Month: Format\$([Time Card Details].DateWorked,'mmmm yyyy')

I want it to only show one month and year. Right now it shows all data.

How do I prompt the user and ask for the month and year and only give that year. I have tried variations [Enter Month] And [Enter Year], but get blank query results. I also have a section of the query that is

Year([Time Card Details].DateWorked)*12+DatePart('m',[Time Card Details].DateWorked)-1

and I don't understand what this is doing.

Any Help would be appreciated.

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
The Year expression cited...don't know! It looks like its taking the year times 12 plus then month minus 1. For 2/2/2000 that would be:
2000*12+2-1 = 24001. Don't know how that is significant...anybody else??

As far as getting user input for month and year, one idea is to create expression columns to calc the month and year using Month() and Year() functions

Then prompt them in the criteria for this information indicating the proper format...

Exp1: Month([Time Card Details].DateWorked)
Totals row: Where
Criteria row: [Enter month (i.e. 01, 02, 03...)]

Exp2: Year([Time Card Details].DateWorked)
Totals row: Where
Criteria row: [Enter year (i.e. 2001, 2002, 2003...)]

Your Date Worked by Month expression will still calc correctly, but should only pick those dates that are in the month/year chosen by the user.

Max

Replies
12
Views
411
Replies
3
Views
93
Replies
1
Views
279
Replies
3
Views
383
Replies
3
Views
227

1,203,610
Messages
6,056,291
Members
444,855
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back