MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Replace date with a different format in same cell?

  Asked By: Lucina    Date: Feb 18    Category: MS Office    Views: 951

Suppose Cell B1 contains "17 Jul 1832" in text format. (It would have to
be text to get around the 1900 date problem.)

I can make a macro to change that format, but to another cell. I.e., in
Cell K1, I could say: =myformat(B1) and it will be "1832 Jul 17"
without quotes.
By going to another cell, say Cell L1, and doing a VLOOKUP, I can change
that to "1832-07-17" without quotes, which is my preferred format. I
prefer the format to be yyyy-mm-dd.

Now my question. Rather than do this over several other cells, could a
macro be written so that I can click on cell B1, enter the macro, and it
will convert from
17 Jul 1832 to 1832-07-17 all in the same cell? (Probably not, but if it
can be done, it would same some time!)



8 Answers Found

Answer #1    Answered By: Ella Brown     Answered On: Feb 18

That first date, Is it consistently "formatted" as "nn mmm yyyy"?

Answer #2    Answered By: Liam Bouchard     Answered On: Feb 18

Yes it is. The cell  is either blank, or, contains a date  of the format
dd mmm yyyy in text format.

Answer #3    Answered By: Hababah Younis     Answered On: Feb 18

Yes, a single VBA function could certainly return the format  you want
without any intervening cells. Steps you'd need to take:

Pass the original date  as a parameter to the function.

Use the Split function to split it apart at the blanks into a three-element

Use a Case statement to convert Jul to the STRING "07", etc. Do not convert
it to the number 7 or you'll just need to put a leading zero back on it.

Use the Join function to join the bits back up, specifying a hyphen as your

Return this new string from the function (and therefore into the cell).

Answer #4    Answered By: Edfu Massri     Answered On: Feb 18

This should get you going I think. It alters a selection to YYYY-MM-DD.

Sub subYMD()
' Alter a selection to YYYY-MM-DD.

Dim rlSelection As Range
Dim rlCell As Range
Dim slOldDate As String
Dim slNewDate As String
Dim slAllMonths As String
Dim ilMonth As Integer
Dim slNewDateArray() As String

slAllMonths = "JANUARY" & "FEBRUARY" & "MARCH" & "APRIL" _
& "MAY" & "JUNE" & "JULY" & "AUGUST" _

Set rlSelection = Selection

For Each rlCell In rlSelection

slOldDate = rlCell.Value
slNewDateArray = Split(slOldDate)
ilMonth = InStr(slAllMonths, UCase(slNewDateArray(1)))
Select Case ilMonth
Case 1
slNewDateArray(1) = "01"
Case 8
slNewDateArray(1) = "02"
Case 16
slNewDateArray(1) = "03"
Case 21
slNewDateArray(1) = "04"
Case 26
slNewDateArray(1) = "05"
Case 29
slNewDateArray(1) = "06"
Case 33
slNewDateArray(1) = "07"
Case 37
slNewDateArray(1) = "08"
Case 43
slNewDateArray(1) = "09"
Case 52
slNewDateArray(1) = "10"
Case 59
slNewDateArray(1) = "11"
Case 67
slNewDateArray(1) = "12"
End Select
slNewDate = slNewDateArray(2) _
& "-" & slNewDateArray(1) _
& "-" & Format(slNewDateArray(0), "0#")
rlCell.Value = slNewDate

Next rlCell

End Sub

Answer #5    Answered By: Samuel Costa     Answered On: Feb 18

It works on
the currently selected range:

Sub blah()
For Each cll In Selection.Cells
a = Application.WorksheetFunction.Trim(cll.Value)
DateBits = Split(a, " ")
If UBound(DateBits) = 2 Then 'only a very basic checking that there
are two spaces in the trimmed date  string
DateBits(0) = Format(DateBits(0), "0#") ' comment out to return single
digit day of month
UCase(DateBits(1))) + 2) / 3 'month in year as number
DateBits(1) = Format(DateBits(1), "0#") 'comment out to return single
digit month
cll.Value = DateBits(2) & "-" & DateBits(1) & "-" & DateBits(0)
End If
Next cll
End Sub

There'll be some line wrapping above so unwrap them as required.
See also comments in the code.

Answer #6    Answered By: Dirck Jansen     Answered On: Feb 18

Yeah... I thought about checking as well but then my thinking was that
Robert would only select the cells he wanted to alter.

I did think though that some of the months may be written out in full so I
used the full names for the Instr. My experience is that people sometimes
write Jul and sometimes July... Same for Jun / June ... Depending on the
price of eggs or wether it's Monday.

The split takes care of the Trim I think.

I thought it was a nice little problem.

Answer #7    Answered By: Calais Bernard     Answered On: Feb 18

"The split takes care of the Trim I think."
So did I, but on testing (XL2003) it didn't!

Answer #8    Answered By: Calvin Banks     Answered On: Feb 18

Call it brain-fade (or rushing out the door to go and pick up my car from
the mechanic). Yes, you can't use join unless you put the stuff into an
array in the correct order - and that's clearly more trouble than it's
worth. Sorry, 'bout that.

Didn't find what you were looking for? Find more on Replace date with a different format in same cell? Or get search suggestion and latest updates.