Date->Text Format

[Bigfoot17]

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.

 

[LWD3]

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

 

[Bernard Liengme]

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

 

[Rynofasho]

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 savingthe
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'

 

[Mike H]

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

 

[Rick Rothstein]

Leave the date in it but change the format to mmddyy

 

[Bernard Liengme]

Oh dear, I was very wrong! Should have tried before answering!

 

[Bigfoot17]

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.

 

[Bigfoot17]

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.

 

[LWD3]

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.

 

[Dave Peterson]

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

 

[Mike H]

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

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.

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

 

[Rick Rothstein]

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"

 

[Dave Peterson]

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