Access DateValue

Chexpeare

Board Regular
Joined
Mar 22, 2007
Messages
64
I'm running a routine in MS Access that extracts dates from a text field in a financial database. Once the date is extracted, I'm using DateValue() to convert into "mm/dd/yyyy" format in order to then execute calculations on the date (days remaining, days in future, days (in years), et cetera).

Problem: DateValue() converts 12/01/33 into 12/01/1933 instead of the expected 2033, for a bond maturing in the future.

Any suggestions?

Thanks
 
Last edited:

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
enter dates in 4 digits for the years. Don't rely in 2 digits. Access has an automatic switchover point for the century and so you really should be using 4 digits.
 
Upvote 0
enter dates in 4 digits for the years. Don't rely in 2 digits. Access has an automatic switchover point for the century and so you really should be using 4 digits.

That's the whole point! I don't have the choice of using 4 digits. I'm working off of text fields that contain a two digit year. That's all they're giving me to work with.
 
Upvote 0
Why not try using DateSerial instead of DateValue?

Or add the 20 to the year part of the text field?

Perhaps something like this.

RealDate: DateValue(Left([TextDate],InstrRev([TextDate],"/")) & "20" & Right([TextDate],2))
 
Upvote 0
Why not try using DateSerial instead of DateValue?

Or add the 20 to the year part of the text field?

Perhaps something like this.

RealDate: DateValue(Left([TextDate],InstrRev([TextDate],"/")) & "20" & Right([TextDate],2))

I was actually trying out DateSerial and it worked. Here it is...the 1950 is arbitrary on my part because the data I'm working with doesn't go above year 50 until it jumps to 1998.

Adjust Year: DateSerial(IIf(Year([Maturity Date])<1950,Year([Maturity Date])+100,Year([Maturity Date])),Month([Maturity Date]),Day([Maturity Date]))

I didn't try the RealDate solution you mentioned, but an Iif condition should also take care of ignoring changes to desirable dates such as 1999.

Thanks
 
Upvote 0
Why not try using DateSerial instead of DateValue?

Or add the 20 to the year part of the text field?

Perhaps something like this.

RealDate: DateValue(Left([TextDate],InstrRev([TextDate],"/")) & "20" & Right([TextDate],2))

Thank you, Norie!!
I was actually trying out DateSerial and it worked. Here it is...the 1950 is arbitrary on my part because the data I'm working with doesn't seem to beyong year 50.

Adjust Year: DateSerial(IIf(Year([Maturity Date])<1950,Year([Maturity Date])+100,Year([Maturity Date])),Month([Maturity Date]),Day([Maturity Date]))

I didn't try the RealDate solution you mentioned, but an Iif condition should also take care of ignoring changes to desirable dates such as 1999.

Thanks
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

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
Back
Top