CODEWITHSUNDEEP
excelvbadatetimeinteger
matzewe
matzewe

Reputation: 41

Normalize dates in VBA regarding time

I know that this question has been discussed a lot - but I actually haven't found a final answer to this problem. I want "remove" (or rather normalize) the time from a date in my VBA (Excel) script. E.g.:

20.12.2017 15:16

should be transformed to

20.12.2017 00:00

As far as I know, functions as Format() or NumberFormat() only change the appearance/format, but not the value itself. In my case, it is necessary to actually change the value, i.e. to round down from 43089,64 (equals 20.12.2017 15:16) to 43089,00 (equals 20.12.2017 00:00). People very often propose to use int(), others say this might lead to an overflow as integer is limited to 32767 (seems legit) and you should use CLng. But when I apply CLng instead, it will round up to 43090, which is not my goal. Others then again propose to cut the date in strings or use CLng() combined with floor() or rounddown().

Can anybody tell me if I can actually use int() or if this might create an overflow? What would be a safe and performant solution otherwise?

I appreciate your help!

Upvotes: 1

Views: 149

Answers (1)

Comintern
Comintern

Reputation: 22205

Int() returns the same data type as it was passed, so you aren't going to cause an overflow. Fix() is better for this usage though, because of how it handles negative numbers (e.g. dates prior to the epoch date). In the case of negative numbers, Int() returns the next number lower than the input, which has the effect of subtracting a day. Fix() simply removes the non-integer component of a floating point type:

Debug.Print Int(-1.5)   'returns -2
Debug.Print Fix(-1.5)   'returns -1

Upvotes: 4

Related Questions