CODEWITHSUNDEEP
vbaexcel
Ryan Newman
Ryan Newman

Reputation: 856

Difference between DateValue and CDate in VBA

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.

Upvotes: 15

Views: 66831

Answers (3)

Alex
Alex

Reputation: 1642

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)

Upvotes: 3

Chel
Chel

Reputation: 2623

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)).

Upvotes: 34

guitarthrower
guitarthrower

Reputation: 5834

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

Upvotes: 5

Related Questions