Convert date cell to plain text 1

[Roxalia]

Hi,

I would like to convert a date cell with the syntax YYYY-MM-DD to a text cell reading just "YYMMDD" and not the counter number 38720. Anyone have a good clue for a swedish novice...

Roger

 

[PHV]

Why not simply playing with the cell's format ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RoyVidar]

Dates are numbers, just shown with date format. YOu shouldn't need any code for this (well, record it if you need), just hit ctrl+1 (format | cells), then in the custom category (lowest), enter the sweedish format string ([å][å]mdd?)

Roy-Vidar

 

[Roxalia]

I understand that I can convert the cell with the custom category. But I'm going to put this string in a small program and compare it to a file name looking like this 060103.xls. Would it work anyway or would excel use the hidden 38720?

 

[PHV]

Have a look at the Format VBA function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GVF]

I would leave the format alone because regardless of the format, the value of the cell is the timevalue relating to the date entered (in your example the value of the cell is "38720").

Try something like this...

TheYear = Right(Year(ActiveCell.Value),2)
TheMonth = Format(Month(ActiveCell.Value), "00")
TheDay = Format(Day(ActiveCell.Value), "00")
TheNewFileName = TheYear & TheMonth & TheDay & ".xls"
'TheNewFileName = "060105.xls"

 

[Roxalia]

After a good deal of research on the Internet. I did it like this. Little of the code is with help from the macro-recorder. I suppose the code could be more sophisticated.

Thanx,
Roger

Sub Kopiera()
'Skrivet av Roger Håkanson
'
' Join today´s date with ".xls"
' Format the filnamn to match visitor files syntax

Dim filnamn As String
filnamn = Format(Now() - 2, "yymmdd") & ".xls"

'Open the files

ChDir "C:\Passage\Bosse"
Workbooks.Open "c:\passage\Bosse\Sammanställning.xls"
ChDir "C:\Passage"
Workbooks.Open (filnamn)

' Copy the information

Windows(filnamn).Activate
Range("B2:B16").Select
Selection.Copy

' Paste the information

Windows("Sammanställning.xls").Activate
Columns("A:A").Find(What:=Date - 2, LookIn:=xlValues).Activate
ActiveCell.Offset(0, 1).Activate
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False_, Transpose:=True

' Close the files

Windows(filnamn).Close
Windows("Sammanställning.xls").Close savechanges:=True

'Application.Quit

Exit Sub

Errorhandler:
Select Case Err.Number
Case 1005
MsgBox ("Hittar ingen fil med dagens datum")
Case Else
MsgBox (Err.Description)
End Select

End Sub