excel vba cdate options code example

Example: excel vba difference between DateValue and CDate

'Difference between CDate() and DateValue().

'In VBA, these two intrinsic functions seem to do the same
'thing... convert values from different data types into the
'corresponding value of the Date data type.

'But CDate() is more capable; it can retain the time component, if
'the time is included in the source data:

MsgBox CDate(Now)
'will display something like: 3/27/2020 1:04:14 PM 

MsgBox DateValue(Now)
'will not include the time component: 3/27/2020

'The Now() function returns the current date AND time as 
'a Date data type. Both CDate() and DateValue() can handle 
'Date data type inputs, but DateValue() always returns only the date
'component, eliminating the time component.

'DateValue() implicity coerces its argunemt to String. If it can 
'interpret the resulting string as a text date it will, otherwise 
'it returns an error.

'CDate() is again more capable; it tries to interpret the date AND
'time from two perspectives:
'1.) The text string (same as DateValue).
'2.) The numeric value (see below).

'In VBA, the Date data type is handled as a IEEE 64-bit (8-byte)
'floating-point number, which is exactly the same as how the 
'Double data type is managed.

'So values of the Date data type and the Double data type are
'interchangeable with the caveat that Dates are restricted
'to the following roughly 10,000 year
'range of 'January 1, 100' to 'December 31, 9999'.

'But Date data type values are displayed as text even though the
'underlying value is numeric. That is why the two MsgBox examples
'above look like they return text. Date data types display as text
'but the underlying value is actually a floating point number.

'To refer to a Date data type literal in code, surround the literal 
'with # symbols:

MsgBox CDbl(#3/27/2020#)
'will display: 43917

'This number, 43917, is the number of calendar days since 12/30/1899.

'The Date data type places the time component to the right of
'the decimal point... as the fraction of 1 day:

'So 7:15 AM on 3/27/2020 is numerically: 
'43917 + 7/24 + 15/(24*60) = 43917.3020833333

'Thus:
MsgBox CDbl(#3/27/2020 7:15#)
'will display: 43917.3020833333

'So while DateValue() cannot handle any numeric input (other than Date
'data types, since Dates display as text), CDate() can successfully
'return a Date for all numeric data types with values
'in the range: -657434.0 to +2958465.999999999767

'-------------------------------------------------------------------
'As a side note, the DATEVALUE() worksheet function is unrelated
'to the VBA DateValue() function. The DATEVALUE() worksheet function
'returns the integer portion of the above numbers, but only for text 
'arguments that represent dates after December 31, 1899:

=DATEVALUE("January 1, 1900")
'will display: 1