Date Format

[Al]

Hello

Dim strMyDate As String
Dim strMonth As String
Dim strYear As String
If A1 =Year (2009) and A2= month (5) how do I get MyDate = 2009-05.
Maybe my approach is wrong, all input is appreciated.
Thanks!

 

[Eduardo]

Hi,
You can do it with a formula

=A1&"-"&A2

 

[royUK]

I wouldn't declare them as strings, Integer or Long is more
appropriate.

What are you actually entering in the cells?

 

[Al]

Thanks Eduardo
Sorry I didnt give enough details...
I want to use the MyDate later in my code for naming a file. Year and month
will be enter via input boxes.
Something like strMyDate = strYear& "-"&strmonth....

 

[Chip Pearson]

To be a real Excel date, it must be calculated with a year, month, and
day. Otherwise, it is just text. You can, of course, format the date
value to display in any format you want, including only month and
year, but internally it needs a day. In your case, the day value can
be anything, but I would recommend you use the first day of the month.
So, you can use something like


Dim MyDate As String
MyDate = Format(DateSerial(Range("A1"), Range("A2"), 1), "yyyy-mm")
Debug.Print MyDate


This will first calculate the data that is the first day of the month
in A2 in the year A1, and then convert that to a string of the format
"yyyy-mm". Once you use Format to convert it to a string, it ceases to
be a date, so none of the date functions will be able to use that
value. If you need a real date, and format it for display as yyyy-mm,
use

Dim MyDate As Date
MyDate = DateSerial(Range("A1"), Range("A2"), 1)
With Range("A3")
.Value = MyDate
.NumberFormat = "yyyy-mm"
End With

In this case, A3 contains a real date value, that is formatted to
display as yyyy-mm. Regardless of how you display the date, the
underlying value is still a date and can be used in calculations.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)