# Get a text date to be a number

#### mj_stev

##### Board Regular
Good morning,

I have an access database with dates in it that are listed as text and not numbers.

I'm using the following code to pull out the four digits for the year:

SELECT right(Date,4) AS [Year]
FROM TableName
GROUP BY right(Date,4)

The thing is that the year comes back as text. When I dump it into the pivot table, I can't use it as a number.

What can I do to correct this?
Michael

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Norie

##### Well-known Member
Michael

Try this.

SELECT Year(DateValue(Date))
FROM TableName
GROUP BY Year(DateValue(Date))

By the way I would strongly recommend you don't use Date as the name of a field in the table.

Replies
14
Views
2K
Replies
8
Views
304
Replies
1
Views
620
Replies
6
Views
238
Replies
3
Views
462

1,190,948
Messages
5,983,808
Members
439,862
Latest member
FaisalAlTawil

### 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