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

Question

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!

Max Integer vaue is 32767. Define IntMonth as Long, and use DateValue instead of CInt. — BrakNicku
– BrakNicku, Commented Sep 16, 2015 at 11:15
You can also overcome this problem by converting the date to a String before using it as a key. That way, the dictionary key preserves the 'yyyy/mm/dd' format. — Nelus
– Nelus, Commented Sep 16, 2015 at 12:08

Gary's Student · Accepted Answer · 2015-09-16 11:20:35Z

3

A tiny trick!

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

This is because CDate() is REALLY flexible.

answered Sep 16, 2015 at 11:20

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nelus Over a year ago

Fantastic! Realise now that the number exceeded the Integer limit (hence 'overflow' error) - I should've used Long from the start.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related