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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.