How to subtract number of hours from 24 hour timezone in alphanumeric format

[atulsanwal22]

Hi All,

I am working on a project where i need to get the values of a CCY curve for past 6 timesnaps for example if i opt for USD rates for Jul 3, 2020 London 12PM (L1200) then it should give the rates for today L1200,L1100,L1000,L0900,L0800,L0700 (rates will be fetched using excel orcale database connection using select query) which i have achieved using the below two formulas

=LEFT(B3,SEARCH("L",B3))&(VALUE(MID(B3,SEARCH("L",B3)+1,1000))-100)
=IF(LEN(B4)=4,LEFT(B4,1) & "0" & MID(B4,2,100),B4)

But the issue comes when i opt for L0400 or before that because if i opt for L0400 timezone and use the mentioned formula it gives the below result which is incorrect. the last two cells should be L2300 and L2200 from previous day

Incorrect


L0400


L0300


L0200


L0100


L0


L-100



Attached is the output i am expecting. any help on this would be much appreciated.
26899

[SamT]

Why are you shipping midnight (2400 / 0000)

[atulsanwal22]

pardon me, i am not clear with your comment

[SamT]

In F3 you have 1AM... In G3 you have 11 PM... What about 12AM... You skipped an hour... Is that deliberate?

[atulsanwal22]

In F3 you have 1AM... In G3 you have 11 PM... What about 12AM... You skipped an hour... Is that deliberate?

Yes thats deliberate

[SamT]

Alright, this is going to take a little setup on your part. First Format Your Row 3, and any other similar use Rows in your preferred Date Format.

Paste the below code into the sheet's Code Page. I set the Constant "atuNumTimeSnapsToConvert" to 6, but you can change it as needed

Next, Enter into Cell B4 , or any other cell used for this purpose, the desired starting Date+Time, Example "7/4/2020 2:33:45AM". Do not start at midnight, the code will fail. Only the hours part of the time is relevant, the Mins and Secs can be any value

Doubleclick cell B4, or any other cell used for this purpose, to start the code. There is no testing to ensure you double click a proper cell.


Option Explicit

Private Const atuNumTimeSnapsToConvert As Long = 6
Private Const LHour00 As String = "Lhh00"

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
GenerateTimes Target
End Sub


Private Sub GenerateTimesOriginal(ByVal Target As Range)
'Tested: Does not skip midnight
Dim i As Long
For i = 1 To atuNumTimeSnapsToConvert - 1
Target.Offset(, i) = DateAdd("h", -i, Target)
Target.Offset(-1, i) = Target.Offset(, i)
Next i
Target.Offset(-1) = Target
End Sub

Private Sub GenerateTimes(ByVal Target As Range)
'Tested: Skips Midnight
Dim A As Long
Dim i As Long
Dim C As Long

A = MidnightCheck(Target)

For i = 1 To atuNumTimeSnapsToConvert - 1
C = i
If i >= A Then C = i + 1
Target.Offset(, i) = DateAdd("h", -C, Target)

Target.Offset(-1, i) = Format(Target.Offset(, i), LHour00)
Next i
Target.Offset(-1) = Format(Target, LHour00)


End Sub

Private Function MidnightCheck(ByVal Target As Range) As Long
Dim i As Long
MidnightCheck = atuNumTimeSnapsToConvert + 1

For i = 1 To atuNumTimeSnapsToConvert - 1
If DatePart("h", DateAdd("h", -i, Target)) = 0 Then
MidnightCheck = i
Exit Function
End If
Next i
End Function

[snb]

In A1: L0400
In B1:7/3/2020
In C1: =$B$1+(--MID($A$1;2;2)-(ROW($A1)-1+(ROW($A1)>--MID($A$1;2;2))))/24
in C2: =$B$1+(--MID($A$1;2;2)-(ROW($A1)-1+(ROW($A2)>--MID($A$1;2;2))))/24
etc.....

[atulsanwal22]

cant we use the below select case :think:

26908

[atulsanwal22]

cant we use the below select case

Sub selectcase()
Dim time As String
time = Range("B3").Value

Select Case time
Case L1200: Range("C3").Value = "L1100"

Case L1200: Range("D3").Value = "L1000"
Case Else: Range("C3:D3").Value = "Invalid"
End Select

End Sub

[SamT]

Please repost without colors


Sub selectcase()
Dim time AsString
time = Range("B3").Value

SelectCase time
Case L1200: Range("C3").Value ="L1100"

Case L1200: Range("D3").Value ="L1000"
CaseElse: Range("C3http://www.vbaexpress.com/forum/images/smilies/astrosmiley.gif3").Value ="Invalid"
EndSelect

EndSub