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.
Capture.jpg

[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

Capture.jpg

[atulsanwal22]

cant we use the below select case

[COLOR=var(--blue-800)]Sub[/COLOR][COLOR=var(--black-750)] selectcase[/COLOR][COLOR=var(--black-750)]()[/COLOR][COLOR=var(--black-750)]
[/COLOR][COLOR=var(--blue-800)]Dim[/COLOR][COLOR=var(--black-750)] time [/COLOR][COLOR=var(--blue-800)]As[/COLOR][COLOR=var(--black-750)][/COLOR][COLOR=var(--blue-800)]String[/COLOR][COLOR=var(--black-750)]
time [/COLOR][COLOR=var(--black-750)]=[/COLOR][COLOR=var(--black-750)] Range[/COLOR][COLOR=var(--black-750)]([/COLOR][COLOR=var(--red-800)]"B3"[/COLOR][COLOR=var(--black-750)]).[/COLOR][COLOR=var(--black-750)]Value

[/COLOR][COLOR=var(--blue-800)]Select[/COLOR][COLOR=var(--black-750)][/COLOR][COLOR=var(--blue-800)]Case[/COLOR][COLOR=var(--black-750)] time
[/COLOR][COLOR=var(--blue-800)]Case[/COLOR][COLOR=var(--black-750)] L1200[/COLOR][COLOR=var(--black-750)]:[/COLOR][COLOR=var(--black-750)] Range[/COLOR][COLOR=var(--black-750)]([/COLOR][COLOR=var(--red-800)]"C3"[/COLOR][COLOR=var(--black-750)]).[/COLOR][COLOR=var(--black-750)]Value [/COLOR][COLOR=var(--black-750)]=[/COLOR][COLOR=var(--black-750)][/COLOR][COLOR=var(--red-800)]"L1100"[/COLOR][COLOR=var(--black-750)]

[/COLOR][COLOR=var(--blue-800)]Case[/COLOR][COLOR=var(--black-750)] L1200[/COLOR][COLOR=var(--black-750)]:[/COLOR][COLOR=var(--black-750)] Range[/COLOR][COLOR=var(--black-750)]([/COLOR][COLOR=var(--red-800)]"D3"[/COLOR][COLOR=var(--black-750)]).[/COLOR][COLOR=var(--black-750)]Value [/COLOR][COLOR=var(--black-750)]=[/COLOR][COLOR=var(--black-750)][/COLOR][COLOR=var(--red-800)]"L1000"[/COLOR][COLOR=var(--black-750)]
[/COLOR][COLOR=var(--blue-800)]Case[/COLOR][COLOR=var(--black-750)][/COLOR][COLOR=var(--blue-800)]Else[/COLOR][COLOR=var(--black-750)]:[/COLOR][COLOR=var(--black-750)] Range[/COLOR][COLOR=var(--black-750)]([/COLOR][COLOR=var(--red-800)]"C33"[/COLOR][COLOR=var(--black-750)]).[/COLOR][COLOR=var(--black-750)]Value [/COLOR][COLOR=var(--black-750)]=[/COLOR][COLOR=var(--black-750)][/COLOR][COLOR=var(--red-800)]"Invalid"[/COLOR][COLOR=var(--black-750)]
[/COLOR][COLOR=var(--blue-800)]End[/COLOR][COLOR=var(--black-750)][/COLOR][COLOR=var(--blue-800)]Select[/COLOR][COLOR=var(--black-750)]

[/COLOR][COLOR=var(--blue-800)]End[/COLOR][COLOR=var(--black-750)][/COLOR][COLOR=var(--blue-800)]Sub[/COLOR]

[SamT]

Please repost without colors

[COLOR=var(--blue-800)]Sub[/COLOR][COLOR=var(--black-750)] selectcase[/COLOR][COLOR=var(--black-750)]()[/COLOR][COLOR=var(--black-750)]
[/COLOR][COLOR=var(--blue-800)]Dim[/COLOR][COLOR=var(--black-750)] time [/COLOR][COLOR=var(--blue-800)]As[/COLOR][COLOR=var(--black-750)][/COLOR][COLOR=var(--blue-800)]String[/COLOR][COLOR=var(--black-750)]
time [/COLOR][COLOR=var(--black-750)]=[/COLOR][COLOR=var(--black-750)] Range[/COLOR][COLOR=var(--black-750)]([/COLOR][COLOR=var(--red-800)]"B3"[/COLOR][COLOR=var(--black-750)]).[/COLOR][COLOR=var(--black-750)]Value

[/COLOR][COLOR=var(--blue-800)]Select[/COLOR][COLOR=var(--black-750)][/COLOR][COLOR=var(--blue-800)]Case[/COLOR][COLOR=var(--black-750)] time
[/COLOR][COLOR=var(--blue-800)]Case[/COLOR][COLOR=var(--black-750)] L1200[/COLOR][COLOR=var(--black-750)]:[/COLOR][COLOR=var(--black-750)] Range[/COLOR][COLOR=var(--black-750)]([/COLOR][COLOR=var(--red-800)]"C3"[/COLOR][COLOR=var(--black-750)]).[/COLOR][COLOR=var(--black-750)]Value [/COLOR][COLOR=var(--black-750)]=[/COLOR][COLOR=var(--black-750)][/COLOR][COLOR=var(--red-800)]"L1100"[/COLOR][COLOR=var(--black-750)]

[/COLOR][COLOR=var(--blue-800)]Case[/COLOR][COLOR=var(--black-750)] L1200[/COLOR][COLOR=var(--black-750)]:[/COLOR][COLOR=var(--black-750)] Range[/COLOR][COLOR=var(--black-750)]([/COLOR][COLOR=var(--red-800)]"D3"[/COLOR][COLOR=var(--black-750)]).[/COLOR][COLOR=var(--black-750)]Value [/COLOR][COLOR=var(--black-750)]=[/COLOR][COLOR=var(--black-750)][/COLOR][COLOR=var(--red-800)]"L1000"[/COLOR][COLOR=var(--black-750)]
[/COLOR][COLOR=var(--blue-800)]Case[/COLOR][COLOR=var(--black-750)][/COLOR][COLOR=var(--blue-800)]Else[/COLOR][COLOR=var(--black-750)]:[/COLOR][COLOR=var(--black-750)] Range[/COLOR][COLOR=var(--black-750)]([/COLOR][COLOR=var(--red-800)]"C33"[/COLOR][COLOR=var(--black-750)]).[/COLOR][COLOR=var(--black-750)]Value [/COLOR][COLOR=var(--black-750)]=[/COLOR][COLOR=var(--black-750)][/COLOR][COLOR=var(--red-800)]"Invalid"[/COLOR][COLOR=var(--black-750)]
[/COLOR][COLOR=var(--blue-800)]End[/COLOR][COLOR=var(--black-750)][/COLOR][COLOR=var(--blue-800)]Select[/COLOR][COLOR=var(--black-750)]

[/COLOR][COLOR=var(--blue-800)]End[/COLOR][COLOR=var(--black-750)][/COLOR][COLOR=var(--blue-800)]Sub[/COLOR]