Date Format
 

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!

Date Format
 

Hi,
You can do it with a formula

=A1&"-"&A2



"Al" wrote:

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!

Date Format
 

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

What are you actually entering in the cells?


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=103403

Date Format
 

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

What are you actually entering in the cells?


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=103403

Date Format
 

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....



"Eduardo" wrote:

Hi,
You can do it with a formula

=A1&"-"&A2



"Al" wrote:

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!

Date Format
 

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)



On Thu, 4 Jun 2009 05:15:01 -0700, Al
wrote:

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!

Date Format
 

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)



On Thu, 4 Jun 2009 05:15:01 -0700, Al
wrote:

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!