Pfthb

I am extracting a timestamp from a cell in the format like Tue Nov 06 07:33:00 UTC 2018. Now, using vbscript code or vba code I want to subtract some minutes from the above mentioned timestamp. How can I achieve that?

split(split("Tue Nov 06 07:33:00 UTC 2018",":")(1),":")(0)

Will give you the minutes on their own. You can assign the splits to arrays, then rejoin with the correct minutes using `join

Something like so

Public Function addToTimeStamp(strTimeStamp As String, lngMinutes As Long) As String

Dim a() As String

a = Split(strTimeStamp, " ")
a(3) = DateAdd("n", lngMinutes, a(3))

addToTimeStamp = Join(a, " ")

End Function

Without going into VBA, you could separate the time stamp from the date, then work on the time stamp and adjust the date accordingly.

Assuming your dates are in the column A and that the timestamp always take the same structure, starting from row number 2

So first you create a column where there is only the time stamp, that is column B:

=MID(A2,FIND("UTC",A2)-9,8)

07:33:00

This first finds the position of "UTC" within the string then extract 8 characters starting from 9 characters to the left (accounting for the space between the time and "UTC").

Already there you can work on the minutes/hours/seconds.

Hours:

=NUMBERVALUE(LEFT(B2,2))

07

Minutes:

=NUMBERVALUE(Mid(B2,4,2))

33

Seconds:

=NUMBERVALUE(Right(B2,2))

00

You can also extract the date part of the time stamp using the same logic. Column C:

=MID(A2,FIND(B2,A2)-11,10)

Tue Nov 06

Finally you can also combine all of that into an Excel date and do your operations directly on the resulting number (This will ensure that you get a new valid date which account for incrementing/decrementing hours/days/months/years, it will also automatically account for leap years.)

Final date, including the time stamp, Column D:

=DATEVALUE(MID(A2,FIND(B2,A2)-3,2)&"-"&MID(A2,FIND(B2,A2)-7,3)&"-"&RIGHT(A2,4))+NUMBERVALUE(LEFT(B2,2))/24+NUMBERVALUE(MID(B2,4,2))/(24*60)+NUMBERVALUE(RIGHT(B2,2))/(24*60*60)

43410.3145833333

On this final number you can simply increase/decrease the number of minutes by adding it directly to it. The unit of this number is "days" so one minute is equal to 1/(24*60) and one hour is 1/24. Example of removing 33 minutes:

=D2 - 33/(24*60)

43410.2916666667

Changing the formatting to [dd/mm/yyyy hh:mm] will then result in:

06/11/2018 07:00:00