VBA equivalent of DATEVALUE?

1
votes

The formula function of =DATEVALUE and VBA's DateValue() provide exclusive results. How can I format a date in VBA to obtain the same serialization as I would by using =DATEVALUE?

Example: 1/19/2016, formatted "m/d/yyyy", will return 42388 through =DATEVALUE()

2
excelvba
I just tested both of them on a valid date string. Both returned the same results. You could try Format('date_string',"mm-dd-yyyy") for example. – Scott Holtzman
@ScottHoltzman - I failed to clarify; by serialization I mean something along the lines of how the date 1/19/2016 would become 42388. – sirius_pain
Just change the NumberFormat of the target cell to "General" – Scott Craner
@ScottCraner - I need a means of doing a conversion from within the macro editor; cells cannot be altered. – sirius_pain
In vba: Range("A1") = "1/1/2016" then Range("A1").NumberValue = "General" – Scott Craner

2 Answers

4
votes

Use CLng e.g.

=DATEVALUE("7/7/16")

Returns

42558

In VBA:

? CLng(DateValue("7/7/16"))
 42558
2
votes

Several ways to do this. @Robin has a good one. I'll add one more

?DateDiff("d",0, "7/7/16")
42558

The DateDiff function calculates the difference between the two dates (2nd and 3rd parameters) in the units of the first parameter. So if you changed "d" to "h" you would get the number of hours between 0 (the first possible date in Excel/VBA) and 7/7/2016