I have a cell (Buttons!B4) that has a date (12/5/08) in it formatted to show
as 5-Dec.

What I would like to do is have this date, or any date in this cell, as a
6-digit text string (120508) so I can use it in the file name when saving the
file. Any suggestions appreciated.

"Bigfoot17" wrote:

I have a cell (Buttons!B4) that has a date (12/5/08) in it formatted to show
as 5-Dec.

What I would like to do is have this date, or any date in this cell, as a
6-digit text string (120508) so I can use it in the file name when saving the
file. Any suggestions appreciated.

try strDate = Format(Range("Buttons!B4").Value,"mmddyy")

You could then use strDate in your file name: "Prefix" & strDate & ".xls"

This would concatenate a file name of Prefix120508.xls

You cannot format it that way but you can generate text with value 120508
using formula
=TEXT(MONTH(B3),"00")&(TEXT(DAY(B3),"00")&TEXT(MOD (YEAR(B3),1000),"00"))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Bigfoot17" wrote in message
...
I have a cell (Buttons!B4) that has a date (12/5/08) in it formatted to
show
as 5-Dec.

What I would like to do is have this date, or any date in this cell, as a
6-digit text string (120508) so I can use it in the file name when saving
the
file. Any suggestions appreciated.

On Dec 5, 2:13*pm, Bigfoot17
wrote:
I have a cell (Buttons!B4) that has a date (12/5/08) in it formatted to show
as 5-Dec.

What I would like to do is have this date, or any date in this cell, as a
6-digit text string (120508) so I can use it in the file name when saving the
file. *Any suggestions appreciated.

Right click on the cell and choose format cells. Then choose Custom
as your format and for type, type in 'mmddyy'

Hi,

Apply a custom format of

mmddyy

You will have to refer to it as

ActiveSheet.Name = Range("A1").Text

using .value will give an error

Mike


"Bigfoot17" wrote:

I have a cell (Buttons!B4) that has a date (12/5/08) in it formatted to show
as 5-Dec.

What I would like to do is have this date, or any date in this cell, as a
6-digit text string (120508) so I can use it in the file name when saving the
file. Any suggestions appreciated.

Leave the date in it but change the format to mmddyy

--
Rick (MVP - Excel)


"Bigfoot17" wrote in message
...
I have a cell (Buttons!B4) that has a date (12/5/08) in it formatted to
show
as 5-Dec.

What I would like to do is have this date, or any date in this cell, as a
6-digit text string (120508) so I can use it in the file name when saving
the
file. Any suggestions appreciated.

Oh dear, I was very wrong! Should have tried before answering!
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Bernard Liengme" wrote in message
...
You cannot format it that way but you can generate text with value 120508
using formula
=TEXT(MONTH(B3),"00")&(TEXT(DAY(B3),"00")&TEXT(MOD (YEAR(B3),1000),"00"))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Bigfoot17" wrote in message
...
I have a cell (Buttons!B4) that has a date (12/5/08) in it formatted to
show
as 5-Dec.

What I would like to do is have this date, or any date in this cell, as a
6-digit text string (120508) so I can use it in the file name when saving
the
file. Any suggestions appreciated.

It seems things got off track on this one, which means I wasn't clear enough.
Several suggestions are telling me how to format the cell. Well, I posted
this in the programming section because I am trying to understand how I can
manipulate the contents of the cell. The data is place in the cell in the
format that is needed there, but I want to use this date elsewhere in VBA.

Using cdate=Range("D6").Text gives me "5-Dec" and I am assuming that is
because the cell is formatted that way. I am trying to extract a 6-character
string no matter what the date. [i.e., "1-Jan" would yield "010108"].

Bernard Liengme seemed to be heading where I wanted to go and then retracted
his suggestion.

"Mike H" wrote:

Hi,

Apply a custom format of

mmddyy

You will have to refer to it as

ActiveSheet.Name = Range("A1").Text

using .value will give an error

Mike


"Bigfoot17" wrote:

I have a cell (Buttons!B4) that has a date (12/5/08) in it formatted to show
as 5-Dec.

What I would like to do is have this date, or any date in this cell, as a
6-digit text string (120508) so I can use it in the file name when saving the
file. Any suggestions appreciated.

"Bigfoot17" wrote:

It seems things got off track on this one, which means I wasn't clear enough.
Several suggestions are telling me how to format the cell. Well, I posted
this in the programming section because I am trying to understand how I can
manipulate the contents of the cell. The data is place in the cell in the
format that is needed there, but I want to use this date elsewhere in VBA.

I stand by my suggestion.

Dim myStr as string
mystr = format(worksheets("buttons").range("B4").value,"mm ddyy")


Bigfoot17 wrote:

I have a cell (Buttons!B4) that has a date (12/5/08) in it formatted to show
as 5-Dec.

What I would like to do is have this date, or any date in this cell, as a
6-digit text string (120508) so I can use it in the file name when saving the
file. Any suggestions appreciated.

--

Dave Peterson

Use this instead

as I said in my last post on the worksheet format as
mmddyyyy

then use

mytext = Range("D6").Text
ActiveSheet.Name = mytext


or leave the worksheet date formatted as mm/dd/yy and in the code use

mytext = Format(Range("D6").Text, "mmddyyyy")
ActiveSheet.Name = mytext

using cdate as the variable won't work

Mike


"Bigfoot17" wrote:

Using cdate=Range("D6").Text gives me "5-Dec" and I am assuming that is
because the cell is formatted that way. I am trying to extract a 6-character
string no matter what the date. [i.e., "1-Jan" would yield "010108"].

Bernard Liengme seemed to be heading where I wanted to go and then retracted
his suggestion.

"Mike H" wrote:

Hi,

Apply a custom format of

mmddyy

You will have to refer to it as

ActiveSheet.Name = Range("A1").Text

using .value will give an error

Mike


"Bigfoot17" wrote:

I have a cell (Buttons!B4) that has a date (12/5/08) in it formatted to show
as 5-Dec.

What I would like to do is have this date, or any date in this cell, as a
6-digit text string (120508) so I can use it in the file name when saving the
file. Any suggestions appreciated.

Dates are stored in VBA the same way as in Excel... as a Double. The integer
portion of the date represents the number of days from "date zero" (which in
VB is 12/30/1899) and the decimal portion of the number is the fraction of a
24-hour day. The best way to work with dates in VB is to keep them in a
variable Dim'med as Date and to use the various Date functions that VB
provides to manipulate them. The format of the Date, for example your mmddyy
format would be produced from the Format function, something like this as an
example...

FileName = "My File Dated " & Format(DateVariable, "mmddyy") & ".txt"

--
Rick (MVP - Excel)


"Bigfoot17" wrote in message
...
It seems things got off track on this one, which means I wasn't clear
enough.
Several suggestions are telling me how to format the cell. Well, I posted
this in the programming section because I am trying to understand how I
can
manipulate the contents of the cell. The data is place in the cell in the
format that is needed there, but I want to use this date elsewhere in VBA.

And this looks a lot like the first suggestion you got from LWD3.

Dave Peterson wrote:

Dim myStr as string
mystr = format(worksheets("buttons").range("B4").value,"mm ddyy")

Bigfoot17 wrote:

I have a cell (Buttons!B4) that has a date (12/5/08) in it formatted to show
as 5-Dec.

What I would like to do is have this date, or any date in this cell, as a
6-digit text string (120508) so I can use it in the file name when saving the
file. Any suggestions appreciated.

--

Dave Peterson

--

Dave Peterson