PDA

View Full Version : Solved: Date format help



kbsudhir
11-29-2007, 12:51 PM
Hello,

I want to reverse 10/29/2007 to 20071029.

Is there any may to do it.

EX value of A1 = "10/29/2007" which should be replaced as 20071029.

How can I do it....?????????????????

:banghead: :banghead: :eek:

marshybid
11-29-2007, 01:16 PM
Do you want to do this through VBA or just in excel??

If you want to do it in excel then just use custom formatting and write in yyyymmdd

If you want to do it in VBA then maybe something like

cells.select
format = (Date, "yyyymmdd")

This would select all cells in the worksheet and change numeric data to read in this format.

Hope this might help

Marshybid :yes

lucas
11-29-2007, 01:42 PM
Please use a title for your threads that reflects the problem you are seeking help with.

Help!!!!!!!!!!!!!!!!!!!!!!
Will cause most folks to go right on by your thread without even looking at it. It also helps when others are searching the forum if the title reflect the content of the thread

I have renamed the thread for you this time.

kbsudhir
11-29-2007, 03:50 PM
Thanks Guys.

I am able to crack it, this how I did it.

Sub FormatPO()
Dim cnt As Integer, yr As String, vlu As String, mon As String, day As String, frmpo As String, pon As String, abc As String
cnt = 5
Do Until Range("J" & cnt).Value = ""
vlu = Trim(Range("K" & cnt).Value)
yr = Right(vlu, 4)
mon = Left(vlu, 2)
day = Replace(vlu, yr, "")
day = Replace(day, mon, "")
day = Replace(day, "/", "")
po = Trim(Range("J" & cnt).Value)
po = Replace(po, "-", "")
po = po & "S"
frmpo = po & yr & mon & day
Range("M" & cnt).Value = frmpo
Range("A" & cnt).Value = Range("M" & cnt).Value
cnt = cnt + 1
Loop
End Sub

:thumb

unmarkedhelicopter
11-30-2007, 02:20 AM
Gee ... and marshybid's answer was so elegant and simple !!!

Zack Barresse
11-30-2007, 02:27 AM
If you want to do it in excel then just use custom formatting and write in yyyymmdd
This is - quite obviously - the best way to go.


cells.select
format = (Date, "yyyymmdd")
No need to select anything, in fact it is inefficient...
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, 1).Format = "yyymmdd"
The downside: this will format regular numbers as dates as well. If you ONLY want the Excel recognized dates to be affected, you'd need to loop through the cells and use the IsDate() VBA function to check if it is a date or not (not very efficient, but doable).

HTH