VBA - Date-Time String Replace/Substitute

Question

I have a column which has date and time of emails from outlook. Some dates are in format - January 2, 2020 4:15 PM, January-14-20 12:44 PM, December-24-19 20:15 PM.

I have tried to use Replace and Substitute functions, Replace does work as defined but from the string time is removed. I would like to have all dates as 2019-12-27 3:02 PM.

sub Replace()
 Dim sString, searchString as String
 dim i, LastCell as Long

 LastCell = Range("C" & Rows.Count).End(xlUp).Row
 Set searchRng = Range("C3:C" & lastCell)

 For Each c In searchRng
   sString = c
   searchString = "-"

   If InStr(1, sString, searchString, vbTextCompare) > 0 Then
      i = InStr(1, sString, SearchString, vbTextCompare)
      i = InStr(i + 1, sString, SearchString, vbTextCompare)
      c.Offset(0, 1) = WorksheetFunction.Replace(sString, i, "19", " 2020")
   End If
 Next c
End Sub

are the "dates", true dates or text strings that look like dates? If you change the number format of the cell to "General" does the date change to a number or does it remain the same? — Scott Craner
– Scott Craner, Commented Feb 20, 2020 at 15:08
Where DataString is declared and when does it receive a value? — FaneDuru
– FaneDuru, Commented Feb 20, 2020 at 15:09
Note that dim i, LastCell as Long only declares LastCell As Long but i As Variant. In VBA you need to specify a type for every variable: Dim i As Long, LastCell as Long otherwise it is Variant by default. Same for your string variables. — Pᴇʜ
– Pᴇʜ, Commented Feb 20, 2020 at 15:18

Pᴇʜ · Accepted Answer · 2020-02-20 15:59:06Z

2

A really safe way to do it without letting Excel guess:

Use a Regular Expression to parse and split the date into pieces (eg using this pattern):

And then use the DateSerial function and TimeSerial function to create a real date value:

Dim RealDateTime As Date
RealDateTime = DateSerial(2020, 2, 20) + TimeSerial(16, 50, 22)

That can be written as real date value into a cell:

Range("A1").Value = RealDateTime

And formatted how ever you like (because it is a real date value)

Range("A1").NumberFormat = "yyyy-mm-dd hh:mm AM/PM"

edited Feb 20, 2020 at 15:59

answered Feb 20, 2020 at 15:52

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ron Rosenfeld · Accepted Answer · 2020-02-20 18:49:47Z

1

CDate will convert a date/time that is a string into a real date/time that can be formatted.

So code like:

For Each c In rngSrc
    c.Offset(0, 1) = CDate(c)
    c.Offset(0, 1).NumberFormat = "yyyy-mm-dd h:mm AM/PM"
Next c

will convert

edited Feb 20, 2020 at 18:49

answered Feb 20, 2020 at 15:24

Ron Rosenfeld

62.1k7 gold badges35 silver badges72 bronze badges

11 Comments

Foxfire And Burns And Burns Over a year ago

Why not do it in 1 line? something like c.Offset(0, 1)=Format(Cdate(c.value),"yyyy-mm-dd hh:nn AM/PM")

Ron Rosenfeld Over a year ago

@FoxfireAndBurnsAndBurns The Format function returns a string. My goal is to return a real date (serial number) that can be manipulated like a date.

Ron Rosenfeld Over a year ago

@FoxfireAndBurnsAndBurns And, depending on all of the requirements, I'd probably just format the entire destination range in a single step. But the code above is just for proof of concept.

Pᴇʜ Over a year ago

@RonRosenfeld Maybe it is worth to mention that if you let Excel convert a string into a date that Excel just guesses and you have no influence what Excel actually does. Here in this example it is pretty clear and therefore it works. But if your string looks like 02/03/2020 the result is not clear (could be mm/dd/yyyy or dd/mm/yyyy) and Excel might guess wrong! • Actually the only safe method is to parse and split the string and create a real date/time with DateSerial/TimeSerial.

Ron Rosenfeld Over a year ago

@Pᴇʜ Yes, one would need to add some testing to ensure that CDate is not returning an error, if that is a possibility and if this is the spot to check it. As I wrote in another comment, the code I provided is proof of concept and not finished code for distribution.

|

Foxfire And Burns And Burns · Accepted Answer · 2020-02-20 15:37:57Z

0

I still think you could do this easier:

'
'
'
 LastCell = Range("C" & Rows.Count).End(xlUp).Row
With Range("C3:C" & lastCell).Offset(0, 1)
    .FormulaR1C1 = "=TEXT(RC[-1],""yyyy-mm-dd hh:mm AM/PM"")"
    .Value = .Value
End With

NOTE: yyyy-mm-dd hh:mm AM/PM will work according to your regional settings, so if Excel is not in english, translate it. My excel is spanish, and this code would not work for me unless I type aaaa-mm-dd hh:mm AM/PM instead of yyyy-mm-dd hh:mm AM/PM

Convert all dates at once.

Range

answered Feb 20, 2020 at 15:37

Foxfire And Burns And Burns

12.1k2 gold badges19 silver badges43 bronze badges

Collectives™ on Stack Overflow

VBA - Date-Time String Replace/Substitute

3 Answers 3

Comments

11 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related