CODEWITHSUNDEEP
excelvbadatenumbersformat
Nelus
Nelus

Reputation: 115

How to change from date format to number in Excel VBA?

I store items in a dictionary with the key being a date e.g. '2012/05/11' The key is then changed to a number (41040) by default. When I want to retrieve that item, I need to reference it by the number: dFRmonths.Item(41040). Since dFRmonths.Item("2012/05/11") does not exist.

I only have the date, not the number, so I need to convert the date to a number, then use the number to do the lookup.

Trying to convert a date to a number gives an 'overflow' error:

IntMonth = CInt("2012/05/11")

Any idea how to change "2012/05/11" into 41040 so I can look it up?

Thanks!

Upvotes: 2

Views: 11230

Answers (1)

Gary's Student
Gary's Student

Reputation: 96791

A tiny trick!

Sub dural()
    IntMonth = CLng(CDate("2012/05/11"))
    MsgBox IntMonth
End Sub

This is because CDate() is REALLY flexible.

Upvotes: 3

Related Questions