De-convert Dates
 

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

De-convert Dates
 

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

De-convert Dates
 

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!

De-convert Dates - Again!
 

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

De-convert Dates - Again!
 

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

De-convert Dates - Again!
 

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!

De-convert Dates - Again!
 

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

Once again, Isabelle - it worked great!


thank you, i'm glad i could help you

isabelle

De-convert Dates - Again!
 

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

De-convert Dates - Again!
 

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

De-convert Dates - Again!
 

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!