EXCEL MS Query create derived date fields

JPL3

New Member
Joined
Jan 23, 2006
Messages
25
Have an application in ACCESS that I want to move to EXCEL, but I can't figure out how to do things in the EXCEL QBE grid. Need to create several alias fields from a date field, such as Year, Month Number, Day of week Number, etc. Trying to add new column using the following to create alias and the year: Year:Date.Year( but I can't enter the column name that contains the date. If I try to use the drop down menu to get the date field it just replaces everything I've entered. And I can't manually enter it. (Note that the date field is a date field, not text) I have combed the web and can't find any tutorial that really explains the EXCEL QBE Grid. Been using ACCESS for a number of years and there is information all over the place for that. I can connect to the proper database, do a join and get the columns I need, I just can't create the 7 or so date fields I need. The data will be loaded into a Pivot Table on open.

Thanks

jpl3
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,088
I've never seen the QBE UI in Excel; does it have one?

You can edit the SQL command text to insert the required alias fields. For example, to derive the Year and Month number from a Date field:

SELECT `Events$`.`Start Date`, Year(`Events$`.`Start Date`) AS 'Year', Month(`Events$`.`Start Date`) AS 'MonthNum' FROM `Events$` `Events$`

In Excel this is Data tab -> Connections -> Query from Excel files -> Properties -> Definition tab -> Command text.
 

JPL3

New Member
Joined
Jan 23, 2006
Messages
25
I've never seen the QBE UI in Excel; does it have one?

You can edit the SQL command text to insert the required alias fields. For example, to derive the Year and Month number from a Date field:

SELECT `Events$`.`Start Date`, Year(`Events$`.`Start Date`) AS 'Year', Month(`Events$`.`Start Date`) AS 'MonthNum' FROM `Events$` `Events$`

In Excel this is Data tab -> Connections -> Query from Excel files -> Properties -> Definition tab -> Command text.

Using EXCEL 365 and don't have > connections-> so I am stopped there. have Queries and connections and it opens a blank window. Also, your example looks a lot like the SQL from ACCESS. Can I just copy that into someplace I don't know how to get to? Thanks for the response, it was helpful.
 

Forum statistics

Threads
1,148,294
Messages
5,745,944
Members
423,985
Latest member
sayed manzar

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top