I have a Word doc that is an export of a long chat history and each entry includes a 13-digit UNIX timestamp. I need to convert each timestamp to conventional/readable date/time (e.g. Friday, January 17, 2025 2:47 AM EST) and append the conventional conversion after the original timestamp. I have a macro that MOSTLY works but my loop isn't iterating properly.
The macro is intended to:
I can't figure out how to get the Range variable to paste/enter the appended value to the document and then iterate to find the next timestamp (I'm too inexperienced to know what I'm doing wrong)
For example the first timestamp is "1702654591899" and the intended replacement text is: "1702654591899; Friday, December 15, 2023 - 10:36:32 AM EST" and I want to repeat for the next timestamp, iterated until the end of the document.
Here is the code I have with comments:
'Function converts UNIX 13 Digit Timestamp to date/time in Eastern Standard Time
'Subtracts 18,000,000 miliseconds to subtract 5 hours for Eastern Standard Time
'Adapted from FaneDuru's Solution at:
'https://stackoverflow.com/questions/73011816/excel-vba-convert-unix-timestamp-to-date-time
Function fromUNIX13DigitsEST(uT) As Date
''Original line that converts to UTC or GMT (I'm not sure which but it's
''5 hours later than intended so I commented it out but left it for info purposes)
'fromUNIX13DigitsEST = CDbl(uT) / 86400000 + DateSerial(1970, 1, 1)
'Modified from line above to account for Eastern Standard Time
fromUNIX13DigitsEST = (CDbl(uT) - 18000000) / 86400000 + DateSerial(1970, 1, 1)
End Function
'Loops and Finds all 13 digit Unix Timestamps and replaces them with the Timestamp plus
'conventional date/time
Sub FindUnix13DigitTimestampsAndAddConventionalDateAndTime()
Dim TimestampInstance As Range
Dim TimestampAndConverted As String 'Artifact of the learning process kept to prevent loops
Set TimestampInstance = ActiveDocument.Range
With TimestampInstance.Find
'Do I need more of these? Is this section the problem?
.Text = "[0-9]{13}"
.MatchWildcards = True
Do While .Execute(Forward:=True) = True
TimestampInstance.Select
' Used to test before adding the "TimestampInstance.Text = ..." line below
' Sets string variable equal to the original Timestamp and adds the standard date/time/time zone
' Uses the Function above to convert the UNIX TimeStamp
' Kept to use in the MsgBox line below as a failsafe against a runaway loop
' Adapted from FaneDuru's Solution at
' https://stackoverflow.com/questions/73011816/excel-vba-convert-unix-timestamp-to-date-time
TimestampAndConverted = TimestampInstance + "; " + Format(fromUNIX13DigitsEST(TimestampInstance), "dddd, mmmm d, yyyy - hh:nn:ss AM/PM") + " EST"
' PROBLEMATIC LINE THAT PARTIALLY DOES WHAT I INTENDED IT TO DO
' Duplicates the Function call above and adds standard date items
' Meant to replace each 13-digit timestamp with the timestamp plus standard date/time/time zone
' If commented out then loop iterates as intended and the MsgBox below displays each successive
' output from the TimestampAndConverted variable
TimestampInstance.Text = TimestampInstance + "; " + Format(fromUNIX13DigitsEST(TimestampInstance), "dddd, mmmm d, yyyy - hh:nn:ss AM/PM") + " EST"
'Displays variable TimestampAndConverted above in a message box & helps prevent a runaway loop
MsgBox TimestampAndConverted
'
'
' Should there be code here to allow the loop to iterate to next 13-digit timestamp
' or reset "TimestampInstance" variable??
'
'
''This isn't the solution since it ends up replacing the 13-digit timestamps with "" so I commented it out
'TimestampInstance.Text = ""
Loop
End With
End Sub
I managed to get the intended output displayed in a MsgBox for each iteration using a string varaible but when I try to repeat the process with the Range variable and enter the replacement text into the Range it gets stuck on the first timestamp and continually re-adds the conventional date/time in a loop.
Using the first timestamp ("1702654591899") as an example the text it adds afterwards is "; Friday, December 15, 2023 - 10:36:32 AM EST" but the loop just keep re-adding "; Friday, December 15, 2023 - 10:36:32 AM EST" so it ends up as:
"1702654591899; Friday, December 15, 2023 - 10:36:32 AM EST; Friday, December 15, 2023 - 10:36:32 AM EST; Friday, December 15, 2023 - 10:36:32 AM EST; Friday, December 15, 2023 - 10:36:32 AM EST; Friday, December 15, 2023 - 10:36:32 AM EST; Friday, December 15, 2023 - 10:36:32 AM EST; Friday, December 15, 2023 - 10:36:32 AM EST; Friday, December 15, 2023 - 10:36:32 AM EST; Friday, December 15, 2023 - 10:36:32 AM EST; Friday, December 15, 2023 - 10:36:32 AM EST; Friday, December 15, 2023 - 10:36:32 AM EST; Friday, December 15, 2023 - 10:36:32 AM EST..."
at which point I CTRL+Break the loop and end the macro.
How do I add the conventional date/time once after each found timestamp and then continue on to the next timestamp?
Your script is nearly complete. Adding just one line will resolve the issue.
Sub FindUnix13DigitTimestampsAndAddConventionalDateAndTime()
Dim TimestampInstance As Range
Dim TimestampAndConverted As String
Set TimestampInstance = ActiveDocument.Range
With TimestampInstance.Find
.Text = "[0-9]{13}"
.MatchWildcards = True
Do While .Execute(Forward:=True) = True
' TimestampInstance.Select
TimestampAndConverted = TimestampInstance + "; " + Format(fromUNIX13DigitsEST(TimestampInstance), "dddd, mmmm d, yyyy - hh:nn:ss AM/PM") + " EST"
TimestampInstance.Text = TimestampAndConverted
' MsgBox TimestampAndConverted
TimestampInstance.Collapse Word.wdCollapseEnd ' ** change
Loop
End With
End Sub