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:

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

boblarson

MrExcel MVP
Joined
Nov 14, 2008
Messages
1,964
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.
 

Chexpeare

Board Regular
Joined
Mar 22, 2007
Messages
64
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
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))
 

Chexpeare

Board Regular
Joined
Mar 22, 2007
Messages
64
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
 

Chexpeare

Board Regular
Joined
Mar 22, 2007
Messages
64
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,177
Messages
5,600,160
Members
414,367
Latest member
dw970906

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