Returns the passed time since 1 January 1899.
var = DateValue(exp)
| var | : variable |
| exp | : aexp |
This function converts exp and returns a date value in var. The conversion uses the VarDateFromString API and so takes into account the Regional settings of the system rather than the current GB Mode setting (for a GB Mode compliant function, see ValDate).
The expression exp can be a string, date, or date literal. The date literal must use a period (or full stop) separator for date (25.12.2018) regardless of Mode settings.
The value returned to var depends on the variable type for the return value but is Date by default: when var is a Single or Double the number of days since 1 January 1899 is returned; where var is of type Integer, the date part without the time is returned (see Known Issues below); and if var is a String, the date and time are returned as a string.
Local da As Date, db As Double, i As Int, s As String
da = DateValue("25 Jan 2019 11:42") : Print da // 25/01/2019 11:42:00
db = DateValue("25 Jan 2019 11:42") : Print db // 43490.4875
i = DateValue("25 Jan 2019 11:42") : Print i // 43490 - See Known Issues re Integers
s = DateValue("25 Jan 2019 11:42") : Print s // 25/01/2019 11:42:00
Print DateValue(#25.01.2019 11:42:00#) // 25/01/2019 11:42:00
Print VarType(DateValue(#25.01.2019 11:42:00#)) // 7 = Date
The base year, at least for Windows 98, is 1930. This means that years specified with only two digits are interpreted a based to 1930. A year of "29" means 2029, and "31" means 1931. Since this is OLE dependent, located in oleaut32.dll, it cannot be adjusted.
The output of the DateValue function is not affected by Mode Date or Mode Time and separates the date elements with the '/' symbol. To get around this problem and standardise your date format, put the DateValue function inside Date$() or DateTime$() as below:
Mode Date "."
Print DateValue(Date)
Print Date$(DateValue(Date))
When using DateValue to return a converted value to an Integer, if the Time element is greater than 12:00 noon then the date value is rounded up rather than truncated, resulting in the wrong date being returned. To get around this, you can use the Trunc function as shown in the example below:
Local i As Integer
i = DateValue("25 Jan 2019 11.42") : Print i // 43490
i = DateValue("25 Jan 2019 12.42") : Print i // 43491
i = Trunc(DateValue("25 Jan 2019 12.42")) : Print i // 43490
[Reported by Sjouke Hamstra, 30/01/2019]
CDate(), Date, Date$, DateAdd(), DateDiff(), DatePart(), DateSerial(), DateTime$(), DateToDmy, DateToDmyHms, Day(), DayNo(), DmyHmsToDate(), DmyToDate(), HmsToTime(), Hour(), IsDate(), Minute(), Month(), Now, Now$(), TimeSerial(), TimeToHms, TimeValue(), Second(), Week(), WeekDay(), Year()
{Created by Sjouke Hamstra; Last updated: 02/02/2019 by James Gaite}