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, Feb 20 '20 at 15:08
Where `DataString` is declared and when does it receive a value? — FaneDuru, Feb 20 '20 at 15:09
Stil `Replace(DataString,...`. After correcting, I will try `sString = CStr(c.Value)` instead of `sString = c`... — FaneDuru, Feb 20 '20 at 15:12
You did not answer to @ScottCraner. When you change the format of cell to `General`, do you still see the text or do you see a number? — Foxfire And Burns And Burns, Feb 20 '20 at 15:14
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ᴇʜ, Feb 20 '20 at 15:18
Have you tried something like `format(cdate(c.value),"yyyy-mm-dd hh:nn AM/PM")`? Notice that this will work only if your Excel is english (January). If you got a different language, then you have to translate `January` — Foxfire And Burns And Burns, Feb 20 '20 at 15:23
@FoxfireAndBurnsAndBurns I'm in a german Excel and even translating it to german, I couldn't get this type of date string working with `cdate`. So there is a high probability that it is depending on which date format and localization is set in the operating system too. Actually you can get a lucky shot, but nothing reliable. — Pᴇʜ, Feb 21 '20 at 07:53

Pᴇʜ · Accepted Answer · 2020-02-20T15:59:06.150

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"

Ron Rosenfeld · Answer 2 · 2020-02-20T18:49:47.000

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 '20 at 18:49

answered Feb 20 '20 at 15:24

Ron Rosenfeld

53,870
7
28
60

Why not do it in 1 line? something like `c.Offset(0, 1)=Format(Cdate(c.value),"yyyy-mm-dd hh:nn AM/PM")` – Foxfire And Burns And Burns Feb 20 '20 at 15:26
2

@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 Feb 20 '20 at 15:27
1

@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`. – Ron Rosenfeld Feb 20 '20 at 15:29
1

@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`. – Pᴇʜ Feb 20 '20 at 15:36
@Pᴇʜ `CDate` is OK with unambiguous dates, as were given as examples. If you have an ambiguous date, then you need to supply external information to be able to properly parse it. But I don't think that is part of the problem here, so I didn't get into all of the ramifications of using dates in VBA/Excel. – Ron Rosenfeld Feb 20 '20 at 15:52
@RonRosenfeld Yes, that's what I said, here it works! Just mentioned it that no one wonders that it doesn't work for other dates :) No, issue here. – Pᴇʜ Feb 20 '20 at 15:54
But actually I get a runtime error 13 type mismatch on `CDate(c)` if they are strings and no dates. At least in a german Excel. So it looks to be not *that* reliable. – Pᴇʜ Feb 20 '20 at 15:58
1

@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. – Ron Rosenfeld Feb 20 '20 at 16:01
@RonRosenfeld After running your code, date - December-24-19 10:58 AM converted to 2024-12-19 10:58 AM. – Cody Feb 20 '20 at 16:32
@Cody I cannot reproduce that issue here. For me, it returns `2019-12-24 10:58 AM` as expected, with the Windows Regional Settings Short date being `DMY` or `MDY`. What are your Windows Regional settings for Short Date? And what is your language? Or perhaps there is something about how you integrated this code portion into the rest of your code? – Ron Rosenfeld Feb 20 '20 at 18:44
@RonRosenfeld Thank you for all the suggestions. However, using Regular Expression method it worked on point for my scenario. – Cody Feb 20 '20 at 19:43

score 0 · Answer 3 · answered Feb 20 '20 at 15:37

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

VBA - Date-Time String Replace/Substitute

3 Answers3