Time and date from string to calculation

[mtheriault2000]

Hello

I got 2 string that represent time as (yyyymmdd hh:mm:ss)

t1 = "20110922 18:57:00"
t2 = "20110922 19:00:02"

Now, I need to calculate

(T2- T1) + T2

What is the step that I need to accomplish to get the result equal to
"20110922 19:03:00"

How do I transform a string date to a string number

Martin

 

[T. Valko]

Hello

I got 2 string that represent time as (yyyymmdd hh:mm:ss)

t1 = "20110922 18:57:00"
t2 = "20110922 19:00:02"

Now, I need to calculate

(T2- T1) + T2

What is the step that I need to accomplish to get the result equal to
"20110922 19:03:00"

How do I transform a string date to a string number

Martin

Are you only interested in the TIME portion of the string for the calculation?

 

[mtheriault2000]

Hello T Valko

No the whole date & time is required

Martin

 

[Rick Rothstein]

Give this formula a try...

=(TEXT(LEFT(T2,8),"0000-00-00")&MID(T2,9,9))-(TEXT(LEFT(T1,8),"0000-00-00")&MID(T1,9,9))+(TEXT(LEFT(T2,8),"0000-00-00")&MID(T2,9,9))

and Custom Format the cell using this pattern...

yyyymmdd hh:mm:ss

 

[T. Valko]

Hello T Valko

No the whole date & time is required

Martin

OK, Rick's suggestion does what you asked for but I have to wonder if that REALLY is what you want.

Now, I need to calculate

(T2- T1) + T2

Consider this:

T1 = 20110922 18:57:00
T2 = 20111122 19:00:02

What result would you expect for that data?

 

[mtheriault2000]

I'm in VBA

Here is the code that I wrote

Sub timedatecalculation()
Dim t1, t2 As String
Dim T1D, T2d, t3, t4 As Date

t1 = "20110922 18:57:00"
t2 = "20110922 19:00:00"
T1D = DateValue(Val(Mid(t1, 1, 4)) & "," & Val(Mid(t1, 5, 2)) & "," & Val(Mid(t1, 7, 2))) & " " & (Mid(t1, 10, 8))
T2d = DateValue(Val(Mid(t2, 1, 4)) & "," & Val(Mid(t2, 5, 2)) & "," & Val(Mid(t2, 7, 2))) & " " & (Mid(t2, 10, 8))


t3 = T2d - T1D

End Sub

I got an incompatible value when i subtract T2d-T1D

Martin

 

[mtheriault2000]

OK, Rick's suggestion does what you asked for but I have to wonder if that REALLY is what you want.




Consider this:

T1 = 20110922 18:57:00
T2 = 20110922 19:00:00

What result would you expect for that data?

I need (T2 -t1) + t2

The end string will have to be : "20110922 19:03:00"

Note: I modified the number of your post

These number represent, on a stock chart,
- The ending date & time of a preceding bar
- The ending date & time of the actual bar
- I want to get the date & time of the next trading bar ending

These number are formated for my broker server and they are read and sent like T1 & T2 format string

Martin

 

[Rick Rothstein]

Sorry, I had mistaken your T1 and T2 to be cell addresses for some reason. Consider doing what you want this way...

T1 = "20110922 18:57:00"
T2 = "20110922 19:00:00"
RealT1 = CDate(Format(T1, "@@@@-@@-@@ @@@@@@@@"))
RealT2 = CDate(Format(T2, "@@@@-@@-@@ @@@@@@@@"))
NewTime = Format((RealT2 - RealT1) + RealT2, "yyyymmdd hh:mm:ss")

 

[mtheriault2000]

Resolved: Time and date from string to calculation

Hello

I did resolve my problem. Thanks to you guy

Sub timedatecalculation()
Dim t1, t2 As String
Dim T1D, T2d, t3, t4 As Date

t1 = "20110922 18:57:00"
t2 = "20110922 19:00:00"
T1D = DateValue(Val(Mid(t1, 1, 4)) & "," & Val(Mid(t1, 5, 2)) & "," & Val(Mid(t1, 7, 2))) + TimeValue((Mid(t1, 10, 8)))

T2d = DateValue(Val(Mid(t2, 1, 4)) & "," & Val(Mid(t2, 5, 2)) & "," & Val(Mid(t2, 7, 2))) + TimeValue((Mid(t2, 10, 8)))

t3 = T2d - T1D + T2d
t3 = Format(t3, "yyyymmdd hh:mm:ss")

End Sub

Martin

 

[Rick Rothstein]

Re: Resolved: Time and date from string to calculation

I did resolve my problem. Thanks to you guy

Sub timedatecalculation()
Dim t1, t2 As String
Dim T1D, T2d, t3, t4 As Date
 
t1 = "20110922 18:57:00"
t2 = "20110922 19:00:00"
T1D = DateValue(Val(Mid(t1, 1, 4)) & "," & Val(Mid(t1, 5, 2)) & "," & Val(Mid(t1, 7, 2))) + TimeValue((Mid(t1, 10, 8)))
 
T2d = DateValue(Val(Mid(t2, 1, 4)) & "," & Val(Mid(t2, 5, 2)) & "," & Val(Mid(t2, 7, 2))) + TimeValue((Mid(t2, 10, 8)))
 
t3 = T2d - T1D + T2d
t3 = Format(t3, "yyyymmdd hh:mm:ss")
 
End Sub

You might want to study my 3-Format-Statements solution... it is much more compact and, in my view, easier to read/maintain (once you understand the @ metacharacter usage that is).