Julian to Gregorian VBA

[OO7Goofy]

I am attempting to solve a problem of finding the difference between two times. The times are downloaded and displayed in a single cell i.e. "8363/0031". The first 4 numbers is the Julian Date and the last 4 are hhmm in 24 hour format. I need to subtract one cell from another and display the difference as HH:MM. For example, if a1 = 8363/0031 and a2 = 8362/2018, I need a1-a2 to print 04:13 in b1.

Thank you in advance for the help.

 

[steve the fish]

Try:

=IF(RIGHT(A2,4)>RIGHT(A1,4),1,0)+TIME(LEFT(RIGHT(A1,4),2),RIGHT(A1,2),0)-TIME(LEFT(RIGHT(A2,4),2),RIGHT(A2,2),0)

 

[shg]

[table="width:, class:grid"][tr][td="bgcolor:#c0c0c0"][/td][td="bgcolor:#c0c0c0"]

a​

[/td][td="bgcolor:#c0c0c0"]

b​

[/td][/tr][tr][td="bgcolor:#c0c0c0"]

1​

[/td][td]8363/0031[/td][td][/td][/tr]
[tr][td="bgcolor:#c0c0c0"]

2​

[/td][td]8362/2018[/td][td][/td][/tr]
[tr][td="bgcolor:#c0c0c0"]

3​

[/td][td="bgcolor:#ccffcc"]

4:13​

[/td][td="bgcolor:#ccffcc"]a3: =abs(left(a1,4) + text(right(a1, 4), "00\:00") - left(a2, 4) - text(right(a2, 4), "00\:00"))[/td][/tr]
[/table]

 

[OO7Goofy]

Thank you for the quick reply. I'm attempting to do this in VBA as the cells I'm comparing are not always in order.

 

[shg]

Sub demo()
  Range("A1").Value = "8363/0031"
  Range("A2").Value = "8362/2018"
  MsgBox Format(DeltaT([a1], [a2]), "h:mm")
End Sub

Function DeltaT(s1 As String, s2 As String) As Date
  ' returns the absolute value of the difference in time between s1 and s2,
  ' which are formatted as d[d..]/hhmm, where dddd is a Julian day number.
  Dim t1 As Date
  Dim t2 As Date
  
  t1 = Left(s1, InStr(s1, "/") - 1) + CDate(Format(Right(s1, 4), "00\:00"))
  t2 = Left(s2, InStr(s2, "/") - 1) + CDate(Format(Right(s2, 4), "00\:00"))
  DeltaT = Abs(t1 - t2)
End Function