Simple (I think) date manipulation

[xirrin]

Hi all - just stumbled upon this forum and am AMAZED by the knowledge that is shared here. What a great community!

I've got a spreadsheet that was downloaded from a vendor in CSV format. When opened the date cell looks like this:


6112010
5152010
9032010
5152010
6122010
10222010

The format is dmmyyyy or ddmmyyyy depending on the month. I need to try and get this in to a standard xx/xx/yyyy or yyyy/xx/xx format. I found in the archives this little ditty:

Sub Macro1()
Dim rg As Range
Dim ocell As Range
Dim temp As String

Set rg = Selection
For Each ocell In rg
temp = Left(ocell, 4) & "/" & Mid(ocell, 5, 2) & "/" & Right(ocell, 2)
ocell = temp
Next
End Sub

I think that could get me mostly there BUT I have the problem of some dates having 2 numbers for the month column (10-12) and some having 1 number (1-9) for the month column. Could someone advise me on how to add a 0 to the front of the date for all of the 1-9 months? I think if I get that I could easily run the above macro and format the date from there. Thanks!

 

[Rick Rothstein]

Does this macro do what you want?

Sub MakeUndelimitedDateStringIntoDate()
  Dim Cell As Range
  For Each Cell In Selection
    Cell.Value = Format(Cell.Value, "0/00/0000")
  Next
End Sub

 

[xirrin]

Does this macro do what you want?

Sub MakeUndelimitedDateStringIntoDate()
  Dim Cell As Range
  For Each Cell In Selection
    Cell.Value = Format(Cell.Value, "0/00/0000")
  Next
End Sub

Aaah! My hero! You are incredible. I knew someone here would have the answer. Thank you so much!

 

[xirrin]

Of course now I'm stuck with another problem that I didn't see coming. I'm guessing this one is incredibly easy though. I just need to hide all the columns that have the string in it ending in L4.

Can you guess what I'm doing? lol

 

[vconfused]

If you didn't want to use VBA, you could use a simple formula like:
=DATE(RIGHT(A1,4),LEFT(A1,LEN(A1)-6),MID(A1,LEN(A1)-5,2))
or
=DATE(RIGHT(A1,4),LEFT(RIGHT("0" & A1,8),2),MID(RIGHT("0" & A1,8),3,2))

 

[xirrin]

If you didn't want to use VBA, you could use a simple formula like:
=DATE(RIGHT(A1,4),LEFT(A1,LEN(A1)-6),MID(A1,LEN(A1)-5,2))
or
=DATE(RIGHT(A1,4),LEFT(RIGHT("0" & A1,8),2),MID(RIGHT("0" & A1,8),3,2))

I'm not quite sure how I would use that formula. I've got about 1200 cells in a column with 8 character strings that have various endings but I just need to eliminate the ones that have L4 as the last 2 characters.

 

[vconfused]

The formulas would just replace the original code -- nothing to do with the L4.

If you are going to be hiding rows/columns then I guess you'll want to stick with code.

So you want to hide, not delete, any rows where a cell in a particular column ends with L4?

Dim rng as range
For each rng in selection
if right(rng.value,2) = "L4" then
rng.entirerow.hidden = true
end if
next rng

 

[xirrin]

The formulas would just replace the original code -- nothing to do with the L4.

If you are going to be hiding rows/columns then I guess you'll want to stick with code.

So you want to hide, not delete, any rows where a cell in a particular column ends with L4?

Dim rng as range
For each rng in selection
if right(rng.value,2) = "L4" then
rng.entirerow.hidden = true
end if
next rng

Money!

You guys are awesome.