Difference between DateValue and CDate in VBA

Question

I am new to VBA and I am working on a module to read in data from a spreadsheet and calculate values based on dates from the spreadsheet. I read in the variables as a String and then am currently changing the values to a Date using CDate. However I just ran across DateValue and I was wondering what the difference between the two functions were and which one is the better one to use.

Answer 1

DateValue will return only the date. CDate will preserve the date and time:

? DateValue("2014-07-24 15:43:06")
24/07/2014

? CDate("2014-07-24 15:43:06")
24/07/2014 15:43:06 

Similarly you can use TimeValue to return only the time portion:

? TimeValue("2014-07-24 15:43:06")
15:43:06 

? TimeValue("2014-07-24 15:43:06") > TimeSerial(12, 0, 0)
True

Also, as guitarthrower says, DateValue (and TimeValue) will only accept String parameters, while CDate can handle numbers as well. To emulate these functions for numeric types, use CDate(Int(num)) and CDate(num - Int(num)).

Answer 2

CDate will convert a number or text to a date.

CDate(41035) 'Converts to a date of 5/6/2012
CDate("1/15/2014") 'Converts to a date of 1/15/2014

DateValue will convert date in text format (only) to a date.

DateValue("1/15/2014") 'Converts to a date of 1/15/2014
DateValue(41035) 'Throws a mismatch error

Answer 3

Both CDate and DateValue, when used in vba, converts a value to a Date (dd/mm/yyyy format):
1)
LstrDate = "July 24, 2014"
LDate = CDate(LstrDate)

2)
LDate = DateValue("July 24, 2014")

Both return the same result.

However DateValue returns the serial number of a date if used in application level (spreadsheet)