Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How to convert 11/5/2004 into11-5-2004

  Asked By: Pedro    Date: Dec 03    Category: MS Office    Views: 597
  

I am a beginner of VBA. Just learned it for two weeks.

I have some problem with my code where I try to change the system date
from 11/5/2004 to 11-5-2004 cause I need that name to be saved as
filename and excel won't accept "/" or "\" as filename.

So I wrote:
==============================================================
Sub filename()

D = Date
Length = Len(n)
Dim i As Integer

If i < Length Then

If Left(D, i) = "/" Then
Left(D, i) = "-"
i = i + 1
End If

End If

MsgBox n

End Sub
================================================================
But it is not replacing "/" with "-". Why?

Can anyone please help me out here, please?

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Estella Mitchell     Answered On: Dec 03

Well, because constructing usable strings from a date is so troublesome,
the good folks at MS did do some other favors for you. Actually, in other
languages, what you're after is normally fulfilled by the use of regular
expressions and replace operations.

For a similar solution, VBA offers the Replace function. Check out this
demo:
'=========================================
Sub GetFullDate()
x = ReplaceChars(Now)
MsgBox x
End Sub
'=========================================
Function ReplaceChars(strString)

strString = Replace(strString, "-", "")
strString = Replace(strString, "/", "")
strString = Replace(strString, ":", "")
strString = Replace(strString, " ", "")

ReplaceChars = strString
End Function
'=========================================

Now, there's still a problem  in that months and days less than 10 cause
the returned string to change, there's that annoying PM or AM on the end
and...it's just not consistent enough. Ick!

So, to deal with this, I like to return a date format constructed in the
YearMonthDaySeconds format and get it so th eresult is always the same
number of characters. I use Seconds to make sure the returned name is
unique instead of checking the file system  and appending characters to
avoid duplicate file names. You can produce that result in a slightly
different way. Refer to the following code:

'=========================================
Sub GetDateString()

strDate = Year(Now) & _
PadZero(Month(Now)) & _
PadZero(Day(Now)) & _
PadZero(Second(Now))

MsgBox strDate

End Sub
'=========================================
Function PadZero(strString)

If Len(strString) < 2 Then
PadZero = "0" & strString
Else
PadZero = strString
End If

End Function
'=========================================

 
Answer #2    Answered By: Felicia Hill     Answered On: Dec 03

Here is the code:

Dim SysDate As String
SystemDate = Format(Now(), "dd-m-yyyy")

 
Answer #3    Answered By: Corinne Rogers     Answered On: Dec 03

Function fncDateString()
fncDateString = Format(Now(),"dd-mm-yyyy")
End Function

Using the Format function to well... format the date.

 
Answer #4    Answered By: Agatha Miller     Answered On: Dec 03

Everyone seems to have helped you with your second question, so I'll tackle
your first.

The Comments are in line.


> I am a beginner  of VBA. Just learned it for two weeks.
>
> I have some problem  with my code  where I try to change  the
system  date
> from 11/5/2004 to 11-5-2004 cause I need that name to be saved as
filename  and excel  won't accept  "/" or "\" as filename.
>
> So I wrote:
> ==============================================================
> Sub filename()
>
> D = Date

To start with, you are using a date field rather than a string, this is not
a big issue because it will still work.

length  = Len(n)

You are trying to determine the length of the date by using the wrong
variable. "n" is undefined so your result will be zero.

You should use
Option Explicit
To require that all variables must be defined.

> Dim i As Integer
>
> If i < Length Then

This is a single pass, so will only handle one replacement. You should put
these statements inside a For loop.
For i = 1 to Length

' the code

Next i

The second problem is that i will have a value of zero which means the Left
function will return an empty string.

> If Left(D, i) = "/" Then
> Left(D, i) = "-"
> i = i + 1
> End If

If you had set i initially to 1 and the left character was a "/" it would
have worked, but when you tried the next character you would be comparing
two characters. The Left function will select characters from the left of
the string up to the ith character. So your comparisons for "06/11/04" would
be
""
"0"
"06"
"06/"
"06/1"
"06/11"
Rather than Left you should be using Mid(D,i,1)

> End If

This is not a loop, so you end up dropping out of the code without comparing
the next character.


> MsgBox n
>
> End Sub
> ================================================================
> But it is not replacing  "/" with "-". Why?
>
> Can anyone please help  me out here, please? Thanks.

When trying to understand VBA code, open the "locals" window and use F8 to
step through the code. If you are using functions like Left(D,i), add an
extra statement where you assign it to a variable. The variable will show up
in the "locals" window and you will know the current value.

 
Didn't find what you were looking for? Find more on How to convert 11/5/2004 into11-5-2004 Or get search suggestion and latest updates.




Tagged: