Hi,
I receive a list of passwords on CSV file that may get converted to a date format, which renders the passwords unusable. For example, one of the passwords may look like "Jun-02" and the content of the cell is "6/1/7502." However, the actual password is "june7502." My question is, how do I de-convert the date format (via VBA), so that the password is in the correct format?
James

hi James,

m = Month(Range("A1"))
y = Year(Range("A1"))
mmm = Array("january", "february", "march", "april", "may", "june", "july",
"august", "september", "october", "november", "december")
MsgBox mmm(m - 1) & y

isabelle

Le 2015-07-02 11:22, a écrit :
Hi,
I receive a list of passwords on CSV file that may get converted to a date format, which renders the passwords unusable.
For example, one of the passwords may look like "Jun-02" and the content of the cell is "6/1/7502."
However, the actual password is "june7502." My question is, how do I de-convert the date format (via VBA), so that the password is in the correct format?
James

On Thursday, July 2, 2015 at 12:01:38 PM UTC-5, isabelle wrote:
hi James,

m = Month(Range("A1"))
y = Year(Range("A1"))
mmm = Array("january", "february", "march", "april", "may", "june", "july",
"august", "september", "october", "november", "december")
MsgBox mmm(m - 1) & y

isabelle

Le 2015-07-02 11:22, a écrit :
Hi,
I receive a list of passwords on CSV file that may get converted to a date format, which renders the passwords unusable.
For example, one of the passwords may look like "Jun-02" and the content of the cell is "6/1/7502."
However, the actual password is "june7502." My question is, how do I de-convert the date format (via VBA), so that the password is in the correct format?
James

Thank you, Isabelle - that worked great!

On Thursday, July 2, 2015 at 12:19:37 PM UTC-5, wrote:
On Thursday, July 2, 2015 at 12:01:38 PM UTC-5, isabelle wrote:
hi James,

m = Month(Range("A1"))
y = Year(Range("A1"))
mmm = Array("january", "february", "march", "april", "may", "june", "july",
"august", "september", "october", "november", "december")
MsgBox mmm(m - 1) & y

isabelle

Le 2015-07-02 11:22, a écrit :
Hi,
I receive a list of passwords on CSV file that may get converted to a date format, which renders the passwords unusable.
For example, one of the passwords may look like "Jun-02" and the content of the cell is "6/1/7502."
However, the actual password is "june7502." My question is, how do I de-convert the date format (via VBA), so that the password is in the correct format?
James

Thank you, Isabelle - that worked great!

I found that I have some Passwords that are showing up in serial date format for example 403776, but the true password is july3005. How can I convert the serial date to the month and last four digits?
James

hi James,

For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
m = Month(Format(Range("A" & i), "m/d/yyyy"))
y = Year(Format(Range("A" & i), "m/d/yyyy"))
mmm = Array("january", "february", "march", "april", "may", "june", "july",
"august", "september", "october", "november", "december")
MsgBox mmm(m - 1) & y
Next

isabelle

Le 2015-07-07 14:04, a écrit :

I found that I have some Passwords that are showing up in serial date format for example 403776,
but the true password is july3005. How can I convert the serial date to the month and last four digits?
James

On Tuesday, July 7, 2015 at 4:01:27 PM UTC-5, isabelle wrote:
hi James,

For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
m = Month(Format(Range("A" & i), "m/d/yyyy"))
y = Year(Format(Range("A" & i), "m/d/yyyy"))
mmm = Array("january", "february", "march", "april", "may", "june", "july",
"august", "september", "october", "november", "december")
MsgBox mmm(m - 1) & y
Next

isabelle

Le 2015-07-07 14:04, a écrit :

I found that I have some Passwords that are showing up in serial date format for example 403776,
but the true password is july3005. How can I convert the serial date to the month and last four digits?
James


Once again, Isabelle - it worked great!

Le 2015-07-07 17:09, a écrit :

Once again, Isabelle - it worked great!


thank you, i'm glad i could help you

isabelle

On Tuesday, July 7, 2015 at 8:26:14 PM UTC-5, isabelle wrote:
Le 2015-07-07 17:09, a écrit :

Once again, Isabelle - it worked great!


thank you, i'm glad i could help you

isabelle

I have one last question. What is the correct way, in VBA, to format the result in the cell? Currently, I'm using the following, but I'm not sure that it's the correct way to do it.
James

Cells(LastRow, 9).Select
With Selection
.NumberFormat = "@"
.Value = mmm(m - 1) & d
End With

Le 2015-07-09 08:49, a écrit :

I have one last question. What is the correct way, in VBA, to format the result in the cell?
Currently, I'm using the following, but I'm not sure that it's the correct way to do it.
James

Cells(LastRow, 9).Select
With Selection
.NumberFormat = "@"
.Value = mmm(m - 1) & d
End With


yes, also for a text format, you can add a single quote before value
Cells(LastRow, 9) = "'" & mmm(m - 1) & d

isabelle

On Thursday, July 9, 2015 at 8:48:02 AM UTC-5, isabelle wrote:
Le 2015-07-09 08:49, a écrit :

I have one last question. What is the correct way, in VBA, to format the result in the cell?
Currently, I'm using the following, but I'm not sure that it's the correct way to do it.
James

Cells(LastRow, 9).Select
With Selection
.NumberFormat = "@"
.Value = mmm(m - 1) & d
End With


yes, also for a text format, you can add a single quote before value
Cells(LastRow, 9) = "'" & mmm(m - 1) & d

isabelle

Thanks again, Isabelle. You're the greatest!